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 –
ID | employee_name | age | salary | Department | manager_name |
---|---|---|---|---|---|
1 | Jimmy | 32 | 80000 | Sales | Vijay |
2 | James | 35 | 85000 | Sales | Vijay |
3 | Helen | 33 | 82000 | HR | Krish |
4 | Mary | 26 | 78000 | Quality | Warner |
5 | William | 33 | 90000 | HR | Krish |
6 | Vijay | 38 | 105000 | Sales | Roger |
7 | Krish | 38 | 105000 | HR | Roger |
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;
Department | COUNT(employee_id) |
---|---|
Sales | 3 |
HR | 3 |
Quality | 1 |
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;
Department | COUNT(employee_id) |
---|---|
Sales | 3 |
HR | 3 |