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.




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.



Trending Articles


Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Web Server vs Application Server Difference Explained

Infix To Postfix Conversion Using Stack in Javascript

SQL Overview

Binary Search Tree Insert node Implementation in Javascript

Evaluate Postfix Expressions Using Stack In Javascript

Linked List Size or Count in Javascript

MYSQL Dump - How to take mysql dump or export mysql data