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 –

 

IDemployee_nameagesalaryDepartmentmanager_name
1Jimmy3280000SalesVijay
2James3585000SalesVijay
3Helen3382000HRKrish
4Mary2678000QualityWarner
5William3390000HRKrish
6Vijay38105000SalesRoger
7Krish38105000HRRoger

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;

DepartmentCOUNT(employee_id)
Sales3
HR3
Quality1

 

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;

DepartmentCOUNT(employee_id)
Sales3
HR3

 

 

 

Leave A Reply

Your email address will not be published. Required fields are marked *