Aggregate Functions & Grouping

So far every query has returned individual rows — one row of output per row of data. Aggregate functions are different: they collapse many rows into a single summary value. This is how you answer questions like "What is the average salary?", "How many employees are in each department?", or "Which month had the highest sales?"

What Are Aggregate Functions?

Think of aggregate functions like a spreadsheet's SUM or AVERAGE formula applied to a column. You give the function a set of rows, and it returns a single number.

We will use this expanded employees table:

Basic Aggregates Without Grouping

When you use an aggregate function without GROUP BY, it operates on the entire table and returns a single row:

SELECT
    COUNT(*)                AS total_employees,     -- count every row
    COUNT(bonus)            AS employees_with_bonus, -- count non-NULL bonuses
    SUM(salary)             AS total_payroll,
    ROUND(AVG(salary), 0)   AS avg_salary,
    MIN(salary)             AS lowest_salary,
    MAX(salary)             AS highest_salary
FROM employees;

Result:

Notice: COUNT(bonus) returns 4, not 6, because Bob and David have NULL bonuses. Aggregate functions (except COUNT(*)) ignore NULL values.

COUNT — Counting Rows

COUNT has three important variants, each with a different meaning:

-- COUNT(*): count every row, including rows with NULLs
SELECT COUNT(*) FROM employees;
-- Result: 6

-- COUNT(column): count rows where that column is NOT NULL
SELECT COUNT(bonus) FROM employees;
-- Result: 4  (Bob and David have NULL, so they are not counted)

-- COUNT(DISTINCT column): count unique non-NULL values
SELECT COUNT(DISTINCT dept) FROM employees;
-- Result: 3  (HR, IT, Finance — three unique departments)

-- Combining them in one query
SELECT
    COUNT(*)              AS total_rows,
    COUNT(bonus)          AS rows_with_bonus,
    COUNT(DISTINCT dept)  AS unique_depts
FROM employees;

Result:

SUM, AVG, MIN, MAX and NULL

All of these functions automatically skip NULL values. This is usually the right behaviour, but be aware of it:

-- SUM ignores NULLs — adds only the 4 non-NULL bonuses
SELECT SUM(bonus) AS total_bonuses FROM employees;
-- Result: 3000 + 5000 + 4000 + 6000 = 18000
-- (NULL values from Bob and David are ignored)

-- AVG ignores NULLs — averages only the 4 non-NULL bonuses
SELECT AVG(bonus) AS avg_bonus FROM employees;
-- Result: 18000 / 4 = 4500  (NOT 18000 / 6 = 3000)

-- If you WANT to include NULLs as zero:
SELECT AVG(COALESCE(bonus, 0)) AS avg_bonus_incl_no_bonus FROM employees;
-- Result: 18000 / 6 = 3000

-- MIN and MAX on dates — returns earliest and latest
SELECT MIN(hire_date) AS first_hired,
       MAX(hire_date) AS most_recently_hired
FROM   employees;
-- Result: 2017-05-14 (Frank) and 2021-09-01 (David)

-- MIN and MAX on strings — returns alphabetically first and last
SELECT MIN(name) AS first_alphabetically,
       MAX(name) AS last_alphabetically
FROM   employees;
-- Result: Alice and Frank

GROUP BY — Summarising by Category

GROUP BY is what makes aggregate functions truly powerful. Instead of summarising the whole table, it groups rows by a column's value and applies the aggregate function to each group separately.

Mental model: Imagine sorting the employees table into three stacks of paper — one stack for HR, one for IT, one for Finance. Then you run COUNT or SUM on each stack independently.

SELECT   dept,
         COUNT(*)           AS headcount,
         SUM(salary)        AS total_payroll,
         ROUND(AVG(salary), 0) AS avg_salary,
         MIN(salary)        AS lowest_salary,
         MAX(salary)        AS highest_salary
FROM     employees
GROUP BY dept
ORDER BY avg_salary DESC;

Result:

The database:

1. Scanned all 6 rows
2. Grouped them into 3 groups by dept
3. Applied each aggregate function to each group
4. Returned 3 summary rows — one per group

The Golden Rule of GROUP BY

Every column in your SELECT list that is NOT an aggregate function must appear in your GROUP BY clause.

-- WRONG: name is in SELECT but not in GROUP BY
SELECT dept, name, COUNT(*)    -- ERROR: name must be in GROUP BY
FROM   employees
GROUP BY dept;

-- CORRECT: only dept is non-aggregate, so only dept is in GROUP BY
SELECT dept, COUNT(*)
FROM   employees
GROUP BY dept;

-- CORRECT: both non-aggregate columns are in GROUP BY
SELECT dept, name, salary      -- no aggregates here
FROM   employees
GROUP BY dept, name, salary;   -- all three must be listed

The reason: each output row from GROUP BY represents a whole group of rows. A group can have many different name values — the database cannot pick just one without you telling it how.

GROUP BY with Multiple Columns

You can group by more than one column to create finer-grained summaries:

-- Group by department AND the year hired
SELECT   dept,
         EXTRACT(YEAR FROM hire_date)  AS hire_year,
         COUNT(*)                       AS headcount,
         AVG(salary)                    AS avg_salary
FROM     employees
GROUP BY dept,
         EXTRACT(YEAR FROM hire_date)
ORDER BY dept, hire_year;

This gives you the number of employees hired in each combination of department and year.

HAVING — Filtering Groups

After GROUP BY creates groups, HAVING filters which groups appear in the result. It is like WHERE, but it runs after grouping and can use aggregate functions.

-- Only show departments with more than 1 employee
SELECT   dept, COUNT(*) AS headcount
FROM     employees
GROUP BY dept
HAVING   COUNT(*) > 1;

Result:

Finance has only 1 employee, so it is excluded.

-- Departments where the average salary exceeds 70000
SELECT   dept,
         ROUND(AVG(salary), 0) AS avg_sal
FROM     employees
GROUP BY dept
HAVING   AVG(salary) > 70000;

Result:

WHERE vs HAVING — The Key Difference

-- Use WHERE for row-level filters, HAVING for group-level filters
SELECT   dept,
         COUNT(*)        AS headcount,
         AVG(salary)     AS avg_sal
FROM     employees
WHERE    hire_date >= '2018-01-01'   -- filter rows BEFORE grouping
GROUP BY dept
HAVING   COUNT(*) > 1               -- filter groups AFTER grouping
ORDER BY avg_sal DESC;

The WHERE runs first and removes employees hired before 2018. Then the remaining rows are grouped, and HAVING removes groups with only 1 employee.

HAVING without GROUP BY

HAVING can technically be used without GROUP BY — in that case the entire table is treated as one group:

-- Returns nothing if average salary is not above 80000, otherwise all employees
SELECT name, salary
FROM   employees
HAVING AVG(salary) > 70000;
-- Result: all 6 employees (because 77500 > 70000)

This is rarely useful in practice. The common pattern is GROUP BY + HAVING.

The Complete Query Execution Order

This is one of the most important things to memorise in SQL. The clauses are written in one order but processed in a different order:

SELECT   dept,
         COUNT(*)           AS headcount,    -- 5: calculate output columns
         AVG(salary)        AS avg_sal
FROM     employees                           -- 1: get source data
WHERE    hire_date > '2017-01-01'            -- 2: filter individual rows
GROUP BY dept                               -- 3: group the remaining rows
HAVING   COUNT(*) > 1                       -- 4: filter groups
ORDER BY avg_sal DESC                       -- 6: sort the output
LIMIT    10;                                -- 7: limit the rows returned

Why this matters:

• WHERE cannot reference SELECT aliases (aliases don't exist yet at step 2)
• HAVING can use aggregate functions (step 4 has access to step 3's groups)
• ORDER BY can reference SELECT aliases (step 6 sees step 5's output)

-- This fails — ORDER BY alias is fine but WHERE alias is not
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  annual_salary > 800000;   -- ERROR: column "annual_salary" unknown at WHERE step

-- Fix: repeat the expression in WHERE
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  salary * 12 > 800000;    -- correct

ROLLUP — Subtotals

ROLLUP extends GROUP BY to also include subtotal rows and a grand total row.

SELECT   dept,
         EXTRACT(YEAR FROM hire_date) AS year,
         SUM(salary)                  AS total_salary
FROM     employees
GROUP BY ROLLUP (dept, year)
ORDER BY dept NULLS LAST, year NULLS LAST;

Result (approximate):

ROLLUP produces subtotals by "rolling up" the rightmost GROUP BY column first, then the next, all the way to the full grand total.

-- Distinguish subtotal rows from real NULLs
SELECT   COALESCE(dept, 'ALL DEPARTMENTS') AS dept,
         COALESCE(CAST(EXTRACT(YEAR FROM hire_date) AS VARCHAR), 'ALL YEARS') AS year,
         SUM(salary) AS total_salary
FROM     employees
GROUP BY ROLLUP (dept, EXTRACT(YEAR FROM hire_date));

CUBE — All Combinations

CUBE goes further than ROLLUP — it generates subtotals for every possible combination of the grouping columns.

SELECT   dept,
         EXTRACT(YEAR FROM hire_date) AS year,
         SUM(salary)                  AS total_salary
FROM     employees
GROUP BY CUBE (dept, year);

With two grouping columns CUBE produces:

• All individual (dept, year) combinations
• Subtotals for just dept (year = NULL)
• Subtotals for just year (dept = NULL)
• The grand total (both NULL)

This is useful for building multi-dimensional summary reports (like pivot tables in Excel).

GROUPING SETS — Custom Combinations

GROUPING SETS lets you specify exactly which groupings you want, without generating all combinations:

-- I want: per-department totals, per-year totals, and a grand total
-- But NOT per (department, year) combinations
SELECT   dept,
         EXTRACT(YEAR FROM hire_date) AS year,
         SUM(salary)                  AS total_salary
FROM     employees
GROUP BY GROUPING SETS (
    (dept),         -- subtotal per department
    (EXTRACT(YEAR FROM hire_date)),  -- subtotal per year
    ()              -- grand total (empty = all rows)
);

This gives you exactly the three types of aggregation you want, without the cartesian combinations that CUBE would produce.

Practical Real-World Examples

Sales by Region and Month

-- Monthly sales summary per region
SELECT   region,
         TO_CHAR(order_date, 'YYYY-MM')  AS month,
         COUNT(*)                         AS num_orders,
         SUM(order_total)                 AS revenue,
         ROUND(AVG(order_total), 2)       AS avg_order_value,
         MAX(order_total)                 AS largest_order
FROM     orders
WHERE    order_date >= '2024-01-01'
GROUP BY region, TO_CHAR(order_date, 'YYYY-MM')
HAVING   COUNT(*) >= 10              -- only months with at least 10 orders
ORDER BY month, region;

Department Headcount and Pay Analysis

-- Salary analysis with salary band distribution
SELECT   dept,
         COUNT(*)                                         AS headcount,
         SUM(salary)                                      AS payroll,
         ROUND(AVG(salary), 0)                            AS avg_salary,
         MAX(salary) - MIN(salary)                         AS salary_spread,
         COUNT(CASE WHEN salary >= 80000 THEN 1 END)      AS senior_count,
         COUNT(CASE WHEN salary < 80000 THEN 1 END)       AS junior_count
FROM     employees
GROUP BY dept
ORDER BY payroll DESC;

Finding Duplicates

-- Find duplicate email addresses (more than one person with same email)
SELECT   email,
         COUNT(*) AS occurrences
FROM     users
GROUP BY email
HAVING   COUNT(*) > 1
ORDER BY occurrences DESC;

Common Mistakes with Aggregates

Mistake 1: Non-Grouped Column in SELECT

-- WRONG: name is not in GROUP BY and is not an aggregate
SELECT dept, name, COUNT(*)
FROM   employees
GROUP BY dept;
-- ERROR: column "name" must appear in GROUP BY or be used in aggregate

-- CORRECT options:
SELECT dept, COUNT(*) FROM employees GROUP BY dept;
-- or, if you want name too:
SELECT dept, name, COUNT(*) FROM employees GROUP BY dept, name;

Mistake 2: Using SELECT Alias in HAVING

-- WRONG: HAVING runs before SELECT, so the alias does not exist yet
SELECT dept, AVG(salary) AS avg_sal
FROM   employees
GROUP BY dept
HAVING avg_sal > 70000;   -- ERROR in most databases (works in MySQL but not standard)

-- CORRECT: repeat the expression in HAVING
SELECT dept, AVG(salary) AS avg_sal
FROM   employees
GROUP BY dept
HAVING AVG(salary) > 70000;

Mistake 3: Aggregate in WHERE Instead of HAVING

-- WRONG: WHERE cannot use aggregate functions
SELECT dept
FROM   employees
WHERE  AVG(salary) > 70000   -- ERROR
GROUP BY dept;

-- CORRECT: aggregates filter with HAVING
SELECT dept
FROM   employees
GROUP BY dept
HAVING AVG(salary) > 70000;

Common Errors

Interview Corner

SELECT COUNT(*)                    AS total_rows,      -- e.g. 107
       COUNT(commission_pct)       AS has_commission,  -- e.g. 35 (non-NULL only)
       COUNT(DISTINCT department_id) AS num_depts      -- e.g. 11
FROM   employees;

-- Wrong — alias 'avg_sal' not yet defined when HAVING runs
SELECT department_id, AVG(salary) AS avg_sal
FROM   employees
GROUP BY department_id
HAVING avg_sal > 8000;   -- ORA-00904 or similar

-- Correct — repeat the expression in HAVING
SELECT department_id, AVG(salary) AS avg_sal
FROM   employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

Related Topics

• Filtering & Conditions — WHERE clause to filter individual rows before grouping
• Joins — aggregate across multiple tables using GROUP BY after joining
• Window Functions — running totals and rankings without collapsing rows
• Subqueries — filter groups using subqueries in HAVING