count() in SQL

The count is used in SQL to return number of rows from Result Set.

Syntax of using count() :

Count () can be used in SELECT statement as well as in GROUP BY clause.

Syntax with SELECT STATEMENT :-

SELECT COUNT(EXPRESSION) FROM TABLE_NAME;

Syntax with GROUP BY CLAUSE :-

SELECT COLUMN_NAME,COUNT(EXPRESSION) FROM TABLE_NAME GROUP BY COLUMN_NAME HAVING COUNT(EXPRESSION) RELATIONAL_OPERATOR VALUE;

We can put three type expressions in Count() and all of them have their different-different meaning:

1) COUNT(*):-

COUNT(*) counts all the values including null values rows.

Example:-Data:-

emid	ename	cmpny
NULL	NULL	0
NULL	NULL	0
NULL	NULL	0

Query:-

SELECT count(*)
  FROM [tb_emp_table]

Result:-

3

Explanation:- Now it is visible that count(*) includes null values as well as it considers all the columns of the table.

2) COUNT(number):-

COUNT(number) counts all the values including null values rows.

Example:-Data:-

emid	ename	cmpny
NULL	NULL	0
NULL	NULL	0
NULL	NULL	0

Query:-

SELECT count(1)
  FROM [tb_emp_table]

Result:-

3

Explanation:- Now it is visible that count(number) includes null values.

3) COUNT(COLUMN_NAME):-

COUNT(COLUMN_NAME) counts all the values excluding null values if present in any row for that column.

Example:-Data:-

emid	ename	cmpny
NULL	NULL	0
NULL	NULL	0
NULL	NULL	0

Query:-

SELECT count(empid)
  FROM [tb_emp_table]

Result:-

0

Explanation:- Query checks each rows values in the empid column and if null value is there then it ignores the value.

note:-

Count takes complex expressions as well like case, if, etc. Example:-

Data:-
 emid	ename	cmpny
 1	    Vihal	tcs
 2	    XYZ0	wipro
 3	    XYZ		3i
 4	    Aarsh	tcs
 5	    XYZ1	tcs
  
   SELECT count(
   case 
   	when cmpny='tcs' then 
   	emid 
   end)
   FROM [tb_emp_table];
   
   result:-
   3