COUNT(): Number of records

SELECT COUNT( * ) AS total_record FROM student

We are using alias here to display the value under heading total_record

SELECT COUNT( * ) AS total_record FROM `student` 
WHERE class = 'four'

SELECT count(*) FROM `student`WHERE class = 'Four' AND mark > 60

Using BETWEEN

SELECT count(*) FROM `student` 
WHERE class = 'Four' AND mark BETWEEN 50 AND 60

Count using unique id field

SELECT COUNT(id) AS total_records FROM student

Counting number of distinct ( unique ) records

SELECT COUNT(DISTINCT class) FROM student

Records between two date ranges ( PHP Script )

$date = new DateTime($dt2);
$dt2 = $date->format('Y-m-d');

SELECT COUNT(S_NO) AS no_tickets FROM main_table WHERE DATE_PL BETWEEN '$dt1' AND '$dt2'

Creating grid view by showing rows as columns

SELECT
COUNT(IF(class = 'Three', 1, NULL)) AS THREE,
COUNT(IF(class = 'Four', 1, NULL)) AS FOUR,
COUNT(IF(class = 'Five', 1, NULL)) AS FIVE,
COUNT(IF(class = 'Six', 1, NULL)) AS SIX,
COUNT(IF(class = 'Seven', 1, NULL)) AS SEVEN,
COUNT(IF(class = 'Eight', 1, NULL)) AS EIGHT,
COUNT(IF(class = 'Nine', 1, NULL)) AS NINE
FROM student

Number of records by linking two tables

By using where condition

SELECT product, COUNT(product_sale.product_id) AS no  
FROM `product`, product_sale 
WHERE product_sale.product_id = product.product_id 
GROUP BY product.product_id

By Left join of tables.

SELECT product, COUNT(product_sale.product_id) AS no  
FROM `product` 
LEFT JOIN product_sale ON product_sale.product_id = product.product_id 
GROUP BY product.product_id

COUNT() handling Null data

1. Counting Rows: When `COUNT()` is used with an asterisk (*) as `COUNT(*)`, it counts all rows in the specified table or view, regardless of null values in any columns. This is because `COUNT(*)` considers the entire row, not individual column values.
2. Counting Specific Column Values: When `COUNT()` is applied to a specific column as in `COUNT(column_name)`, it counts only the non-null values in that column. This means that null values in the specified column are not included in the count.
3. Counting Distinct Values: When used with the `DISTINCT` keyword as in `COUNT(DISTINCT column_name)`, `COUNT()` counts all distinct non-null values in the specified column. Again, null values are not counted because only distinct non-null values are considered.

SELECT IFNULL(class,'Not Known') as class, COUNT(*) AS no  FROM `student3` GROUP BY class;

SELECT IFNULL(class,'Not Known') as class, COUNT(class) AS no  FROM `student3` GROUP BY class;

Showing Number of records while displaying records using select query and PHP MySQLI

<?php
require "config.php"; // Database connection

if($stmt = $connection->query("SELECT id, name, class, mark FROM student")){

  echo "No of records : ".$stmt->num_rows."<br>";

  while ($row = $stmt->fetch_assoc()) {
    echo $row['id'], $row['name'], $row['class'], $row['mark']."<br>";
  }
} else {
  echo $connection->error;
}
?>

PHP MySQLI with SELECT query

echo "<button type='button' class='btn btn-success btn-xs' id='b1'>
No of records <span class='badge'>45</span>
</button>";

Questions

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here