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