SQL Count

Structured Query Language Count() Function is used with Structured Query Language SELECT Statement. SQL Count() function returns the number of items that match the specified criteria in the SELECT statement.

Count() function includes Duplicates values also. If you want to discard the duplicate value count, use the DISTINCT keyword with the Count() function.

Syntax

Here is the syntax of the Count function in Structured Query Language.

SELECT COUNT(*) FROM TABLE_NAME; 

And this syntax will return the total number of records present inside the table.

SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;

We have to mention the column name after the COUNT keyword and the table name in this syntax.

Example

We consider the existing Employees table with certain columns as an example of Count Functions in Structured Query Language.

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	WORKING_HOURS	GENDER
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE	1	12	F
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1	9	M
1003	NIKHIL	VANI	50000	JAIPUR	FMW	2	10	M
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	10	F
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	12	M
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING	4	9	F
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	9	F
3002	ANUJA	WHERE	50500	JAIPUR	FMW	2	9	F
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	12	M
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4	10	M

• Write a query to count the total records from the Employees table.

SELECT COUNT(*)  AS TOTAL EMPLOYEES FROM EMPLOYEES;

The above query shows the following result it means there is a total of 10 records in the Employees table.

• Write a query to count the total department number from the Employees table.

SELECT COUNT(DEPARTMENT) AS TOATLDEPARTMENT FROM EMPLOYEES;

The above query shows the following result. It included duplicate department values also.

Suppose you want to count unique values from the department field, then you will use DISTINCT Keywords inside the Count() function with Column Name.

• Write a query to count the unique department of employees from the Employees table.

SELECT COUNT(DISTINCT DEPARTMENT) AS DEPARTMENT FROM EMPLOYEES;

The above query shows the following result. It shows only 4 counts because there are only 4 unique departments in the Employees table.

• Write a query to count unique cities from the employee's table.

SELECT COUNT(DISTINCT CITY) AS CITY FROM EMPLOYEES;

The above query shows the following result. It shows only 5 counts because there are only 5 unique cities in the Employees table.

• Write a query to count the total number of those employees who belong to city Pune.

SELECT COUNT(*) FROM EMPLOYEES WHERE CITY = ‘PUNE’;

Above query shows the following result:

There are a total of 3 employees who are from Pune city.

• Write a query to count the total number of employees whose first name starts with 'V' and salary is greater than 50000.

SELECT COUNT(*) FROM EMPLOYEES WHERE FIRST_NAME  LIKE ‘V%’ AND SALARY > 50000;

Above query shows the following result:

There are only 2 employees whose first name starts with V and salary is greater than 50000.

• Write a query to count the total numbers of the department from the employee's table joined with the manager table.

SELECT COUNT(DEPARTMENT) AS DEPARTMENT FROM EMPLOYEES INNER JOIN MANAGER ON EMPLOYEES.MANAGERID = MANAGER.MANAGER;

Above query shows the following result:

The above query return counts a total number of departments whose manager id is present in the employee's table from the manager table.