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

Syntax of using count() :

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.

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

love ❤ coding, solving some industry problems technologies: JavaScript, C#, Angular, PLSQL, Docker Want to learn: Python, Go language, AI, ML and Cloud