by Dinesh Thakur Category: SQL Functions

SQL COUNT is the aggregate arithmetic function. COUNT allows us to COUNT number of row that matches specified criteria. This function returns the number of rows in the query. The COUNT function will only count those records in which the field in the brackets is NOT NULL. The SQL COUNT function is easy to use.

SyntaxSELECT COUNT (column_name) FROM table_name WHERE conditions

SQL COUNT Examples We want to know how many employees in the table:
SQL > SELECT count (*) as "TOTAL ROW" from EMP;
TOTAL ROW 
 
14
 

Example of SELECT COUNT (DISTINCT Column Name)
COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. If we want to select the number of unique departments:

SELECT COUNT (DISTINCT JOB) AS Department FROM EMP;

Example of using WHERE Clause

Select how many employees have salary over 1500:

SELECT COUNT (ename) AS Employee FROM Emp WHERE Sal > 1500 ;

 

Employee
 
5
 

Example of using GROUP BY
In some cases, you will be required to use a GROUP BY clause with the COUNT function. Sometime, we may want to know how many employees in each department:

SELECT deptno, COUNT (deptno) AS Employee FROM EMP GROUP BY Deptno;
Deptno Employee 
   
10 3
20 5
30 6


About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.