SQL AGGREGATE FUNCTIONS – COUNT
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;
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’;
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;