Summarising Data — Aggregate (Group) Functions

So far our queries returned individual rows. Often, though, a business wants a single summary number about many rows — 'how many employees are there?', 'what is the total salary bill?', 'what is the average salary?'. These are answered by aggregate functions (also called group functions), which take a whole column of values and return one value. The five standard aggregate functions are:

• COUNT() — counts rows. COUNT(*) counts all rows; COUNT(column) counts only the rows where that column is not NULL.
• SUM(column) — adds up all the (non-NULL) values in a numeric column.
• AVG(column) — the average (arithmetic mean) of the non-NULL values in a numeric column.
• MAX(column) — the largest value in the column.
• MIN(column) — the smallest value in the column.

For the Employee table, the total number of employees:

SELECT COUNT(*) FROM Employee;

This returns 6. But the number of employees whose salary is actually recorded:

SELECT COUNT(Salary) FROM Employee;

This returns 5, because COUNT(Salary) skips the NULL salary of Ipsita Rout — an important difference between COUNT(*) and COUNT(column). The total and average salary paid:

SELECT SUM(Salary) AS TotalSalary, AVG(Salary) AS AverageSalary
FROM Employee;

Here too the NULL salary is ignored, so the average is the total of the five known salaries divided by 5, not by 6. The highest and lowest salaries:

SELECT MAX(Salary) AS Highest, MIN(Salary) AS Lowest
FROM Employee;
``` …