COUNT(): Number of records


Number of records in a table with different conditions by using SQL count() in less than 2 minutes



Count Query Total Number of records in our student table.
SELECT count( * ) as  total_record FROM student
Output of above query is here.
total_record
35
We can add some condition to this SQL to count the records with different conditions. Let us find out the number of students in class Four in out table.

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

SELECT count( * ) as total_record FROM `student` 
WHERE class = 'four'  
Output is here
total_record
9
This will return the count of students in class four only. Here since we are displaying only one count for a fixed class so we can use this way. We may require to display total of each class in a table so here we have to use GROUP BY clause to display totals of each class.

We can count the records in different combinations like finding out the number of students of class four who has got more than 60 mark.
SELECT count(*) FROM `student` WHERE class='Four' and mark >60
Output of above query is here.
count(*)
5

Using BETWEEN

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

Count using unique id field

In above query we have used all the columns to find out the total number of records ( by using count(*) ) . In our student table we have id field which is unique and auto incremented. So if we apply count to this id filed then it will more efficient.
SELECT COUNT(id) AS total_records FROM student

Counting number of distinct ( unique ) records

SELECT COUNT( DISTINCT class ) FROM student
Output is 7

Records between two date ranges ( PHP Script )

We will keep two variables as $dt1 and $dt2 with two dates. Here date must be in Y-m-d format. Here is the code to change the date format and the query.
$date = new DateTime($dt2);
$dt2=$date->format('Y-m-d');
This query will return the number of records found between two date ranges.
SELECT COUNT(S_NO) AS no_tickets FROM main_table
 WHERE  DATE_PL  BETWEEN '$dt1' AND '$dt2' 
Download the SQL dump of the above student table

Creating grid view by showing rows as columns

We will use if condition checking to create a grid view.
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 
Output is here
THREE FOUR FIVE SIX SEVEN EIGHT NINE
3 9 3 7 10 1 2

Number of records by linking two tables

We have two tables, one is storing products (table name product) and other one is storing sales record ( table name sale) . We will get the number of records by linking these 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
Output is here
productno
Monitor 3
CPU1
In above case there is no information about the products for which there is no record available in our sales table.

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
Output is here
productno
Monitor 3
CPU 1
Keyboard 0
Mouse 0
Here are the sql dump of product tables

COUNT() handling Null data

When it comes to handling null values, the behavior of COUNT() depends on how it is used in a query:
  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.
Therefore, the handling of null values by the COUNT() function in SQL is context-dependent, with nulls being ignored when counting specific column values or distinct values but not when counting rows.
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;
Output
COUNT(*)
classno
Four7
Not Known3
Three2
51
Five2
Six7
Seven10
Nine2
Eight1
COUNT(class)
classno
Four7
Not Known0
Three2
51
Five2
Six7
Seven10
Nine2
Eight1
SQL dump of student3 table

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

If you are displaying records of a table then at the same time you can display number of records. Here is the code .
<?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

While showing the number of records we can use bootstrap label to display
echo "<button type='button' class='btn btn-success btn-xs ' id='b1'>No of records <span class='badge'>45</span> </button>";
Generate your own badge code here

Questions



SQL SUM SQL Average SQL MAX
SQL Left Join Linking of more than one table SELECT command to get records

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Nagappan

    06-11-2009

    IT'S VERY NICE
    nayan

    02-04-2010

    hi thanks for giving information thanks again...
    sumit

    27-04-2010

    please tell me the query if i want to count the no. employees from a table emp_detail (and display it) and also need to see the different entries of a different table course_detail ? I need to see them on single web page together..
    Rajan Arora

    25-06-2010

    Really simple and nice way to explain... Gr8 gng... Keep it Up ...
    ragavan

    11-07-2010

    u r done a very good job. i need the answer for how to get the totals of the three columns in the table.
    vikas

    10-10-2010

    how to get the totals of between two given date.
    Bruno

    17-12-2010

    Pls I need a syntax for count with this scenario: patients who visited their gp in the last three months
    Siddharth

    23-12-2011

    how to add a new row of total at the last of all the integer record.. plz help me
    sathya

    23-12-2011

    Pls i need a syntax to increase the count by 1. (e.g): In a shop bill generation, the bill no should increase automatically by 1 at each bill
    dhanasekaran

    04-02-2012

    hi..plz help me out. i like to get count of records in the table ( included deleted recors ). in other words.. the number of records from the table creation.
    Alvin567

    09-08-2012

    Hi there, is it possible to do this? Select count(enabled = 1) from user
    saurabh titus

    30-09-2014

    i have a question. i have a table with 2 fields(name,second field is no of cards)how i calculate all the no of cards with or without using sql,
    it is necessary to use sql or not
    vey

    18-08-2015

    Can i Count Quantity student by Anny subject with sql server
    SMO

    10-02-2017

    You have to use SQL , All database supports SQL to manage data.
    smo1234

    10-02-2017

    By using if condition you can create grid view, this part is added to the main contain of this page.
    adrian

    14-10-2017

    i want the the ouput will be display in a label
    smo1234

    15-10-2017

    Link is added to generate code for label
    Swetha

    15-12-2018

    How to find highest value in the table where no total column we need to find highest without having total column
    smo1234

    16-12-2018

    Use SQL MAX

    04-09-2019


    how to add a new row of total at the last of all the integer record.. plz help me

    14-08-2020

    How to count inserted and deleted rows separately on same page.

    23-08-2020

    You can use MySQL function mysql_affected_rows() to get the number of records deleted or update or inserted after executing the query.

    18-01-2022

    How to select count of record year wise

    15-08-2022

    I have a referral system, email is the referral code it's working but I want to count how many times a value (email) appears in a row as to display to the user how many person he/she have refer.

    22-09-2022

    SELECT count(*) from table_name WHERE email='userid@exampl.com'

    22-09-2022

    SELECT year(date_column),count(*) FROM table_name GROUP BY year(date_column)

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer