Aggregate Functions

Aggregate functions operate on a collection of rows (a group) and calculate a single value that summarizes those rows. They are often used with the SELECT statement to gain insights into the dataset, such as totals, averages, and counts.

1. The Core Aggregate Functions

The five standard SQL aggregate functions are:

Execution Example: Basic Aggregation

Let’s calculate the total number of employees, the total payroll cost, and the average salary from the Employees table.

SELECT
    COUNT(employee_id) AS TotalEmployees, -- Count all IDs
    SUM(salary) AS TotalPayrollCost,      -- Sum of all salaries
    AVG(salary) AS AverageSalary,         -- Average salary
    MAX(salary) AS HighestSalary          -- Highest single salary
FROM Employees;

Note: We use column aliases (AS) to give the resulting single row and single column descriptive names.

2. The COUNT() Function Details

The COUNT() function has two primary uses:

1. COUNT(*): Counts all rows in the result set, including those with NULL values.
2. COUNT(column_name): Counts only the rows where the specified column has a non-NULL value.

Execution Example: Counting Non-NULL Values

If the phone_number column allows NULL values, we can count how many employees have provided a phone number.

SELECT
    COUNT(*) AS TotalRecords,
    COUNT(phone_number) AS EmployeesWithPhone
FROM Employees;

3. Grouping Results (GROUP BY)

When you use an aggregate function, the calculation is performed over all the rows returned by the query. However, if you want to apply the aggregation to subsets of rows (e.g., finding the average salary per department), you must use the GROUP BY clause.

The GROUP BY clause must include every non-aggregated column listed in the SELECT statement.

Execution Example: Aggregation by Group

Assuming the Employees table has a department column, let’s find the number of employees and the average salary for each distinct department.

SELECT
    department,                      -- 1. The grouping column
    COUNT(employee_id) AS EmployeeCount,
    AVG(salary) AS AvgDepartmentSalary
FROM Employees
GROUP BY department;                 -- 2. Group the rows by the department column

4. Filtering Groups (HAVING)

The WHERE clause filters individual rows before aggregation and grouping occurs. The HAVING clause, conversely, filters the results of the groups that have been created by the GROUP BY clause.

Execution Example: Filtering Groups

Using the previous example, let’s filter the results to show only departments that have more than 10 employees.

SELECT
    department,
    COUNT(employee_id) AS EmployeeCount
FROM Employees
GROUP BY department
HAVING COUNT(employee_id) > 10;  -- Filter out groups where the count is <= 10

Note: The HAVING clause uses the aggregate function (COUNT()) in its condition, which is something the WHERE clause cannot do.