SQL AGGREGATE FUNCTIONS – COUNT

SQL AGGREGATE FUNCTIONS – COUNT

SQL has 5 different aggregate functions (MIN, MAX, COUNT, SUM & AVG). Here we will be looking at the COUNT function. The COUNT function returns the total count of values of a column in the table. 

SYNTAX – 

SELECT COUNT(column1) FROM table_name WHERE condition;

Usually, the aggregate functions are used in conjunction with the GROUP BY/ HAVING clause, which gives even more meaningful results. The syntax for the query using COUNT function using GROUP BY / HAVING clause is mentioned below –

SELECT column1, COUNT(column2) FROM table_name WHERE condition1 GROUP BY column1 HAVING condition2;

 

At first, these might look little complicated, but once we go over few examples, it will make more sense. 

Illustration – Consider the below employee table in the database which stores the information pertaining to employees in ABC company –

Query 1 – What is the total number of employees of ABC company?

  SELECT COUNT(employee_id) FROM employee;

COUNT(employee_id)

————— 

7

Query 2 – What is the total number of employees of ABC company who belongs to sales department?

  SELECT COUNT(employee_id) FROM employee WHERE department=’sales’;

COUNT(employee_id)

————— 

3

Query 3 – What is the total number of employees in ABC company by each department.

  SELECT Department, COUNT(employee_id) FROM employee GROUP BY Department;

Query 4 – What is the list of departments in ABC company, which has more than 2 employees in their department ?

  SELECT Department, COUNT(employee_id) FROM employee GROUP BY Department HAVING COUNT(employee_id) > 2;