SQL COUNT Command: Number of records

We can count the number of records in a table with different combinations. Let us first count the total number of records in the table with this count command.
SELECT count( * ) as  total_record FROM student
Output of above query is here.
total_record
35
This will display total records under the name total_record in the table student. Now 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 hading 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
Ouput of above query is here.
count(*)
5

Using BETWEEN

SELECT count(*) FROM `student` WHERE class='Four' and mark between 50 and 60
Oupput 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
Cards 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 sql dump of this student table

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

Number of User Comments : 12


Google+

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

Post Comment This is for short comments only. Use the forum for more discussions.




Subscribe to our mailing list

* indicates required
Subscribe to plus2net




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2016 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer