MYSQL difference between count(*) and count(column_name) mysql-difference-between-count-star-and-count-column-name.pdf

Earlier we have published about Mysql Count Function which is used to return the total number of rows present in any database table. Count function is mainly used where we want to fetch the total number of records in any of the given table.

Problem Statement

So the question arises is that what is the difference between count(*) and count(column_name)?

count(*)

count(*) returns the total number of rows in any table irrespective of the values inside the columns.

count(column_name)

On the other hand count(column_name) returns the count of not null rows for the given column_name i.e. it does not count rows with NULL values in the column.


Examples

Let us have a deeper look with the help of an example.

Consider student_list table

+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
|   1 | Ankur Rastogi | Second | 4 |
|   2 | Amit Kumar | Second | 8 |
|   3 | Ram Kumar | Second | 8 |
|   4 | Shiv Saxena | First | 6 |
|   5 | Aman Aggarwal | Third | 3 |
|   6 | Nipun Gupta | Fourth | 2 |
|   7 | Arpit Gupta | Third | 1 |
|   8 | Shantanu Rastogi | Second | 3 |
|   9 | Ramesh Kumar | Third | 1 |
|  10 | Neelima Dhingra | Second | NULL |
|  11 | Anu Sibbal | Third | NULL |
|  12 | Ram Kumar | Third | 4 |
+-----+------------------+--------+-----------+

Let us see make two queries one with count(*) and another with count(hostel_id).

select count(*) from student_list;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
select count(hostel_id) from student_list;
+------------------+
| count(hostel_id) |
+------------------+
|               10 |
+------------------+

As from these examples you can see that count(*) is giving us 12 records where as count(hostel_id) is giving us 10 records. This is because total number of rows in student_list table are 12 whereas total rows where hostel_id is not null is 10.




This article is contributed by Mayank Rastogi on Thu Nov 03 2016 22:48:52 GMT+0530 (IST)

If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.