Lesson 15 — Summarising Data

Aggregate Functions

Aggregate functions collapse multiple rows into a single summary value — count, sum, average, minimum, maximum. They are the backbone of all reporting and analytics queries.

SQL — Core aggregates 
-- COUNT — how many rows:
SELECT COUNT(*)            AS total_learners  FROM learners;
SELECT COUNT(mark)         AS marked_learners FROM learners;  -- ignores NULLs
SELECT COUNT(DISTINCT city) AS unique_cities  FROM learners;

-- SUM:
SELECT SUM(mark)     AS total_marks FROM learners;
SELECT SUM(mark) / COUNT(*) AS manual_avg FROM learners;  -- same as AVG but educational

-- AVG (ignores NULLs):
SELECT AVG(mark) AS average_mark FROM learners;
SELECT AVG(COALESCE(mark, 0)) AS avg_including_ungraded FROM learners;

-- MIN and MAX:
SELECT MIN(mark) AS lowest, MAX(mark) AS highest FROM learners;
SELECT MIN(enrolment_date) AS first_enrolment, MAX(enrolment_date) AS latest FROM learners;

-- All together:
SELECT
    COUNT(*)          AS total,
    COUNT(mark)       AS marked,
    AVG(mark)         AS avg_mark,
    MIN(mark)         AS min_mark,
    MAX(mark)         AS max_mark,
    SUM(mark)         AS sum_marks
FROM learners;
SQL — Aggregate with WHERE 
-- Aggregate with WHERE — filter rows BEFORE aggregating:
SELECT
    COUNT(*) AS java_learners,
    AVG(mark) AS java_average
FROM learners
WHERE course = 'Java';

-- Calculated aggregates:
SELECT
    COUNT(*) AS total,
    COUNT(CASE WHEN mark >= 50 THEN 1 END) AS passed,
    COUNT(CASE WHEN mark < 50  THEN 1 END) AS failed,
    ROUND(
        COUNT(CASE WHEN mark >= 50 THEN 1 END) * 100.0 / COUNT(*)
    , 1) AS pass_rate_pct
FROM learners
WHERE mark IS NOT NULL;
SQL — Math functions 
-- ROUND, FLOOR, CEIL:
SELECT ROUND(AVG(mark), 1) AS avg_1dp FROM learners;
SELECT FLOOR(AVG(mark))    AS avg_floor FROM learners;
SELECT CEIL(AVG(mark))     AS avg_ceil  FROM learners;

-- STD / STDDEV (standard deviation — not in all engines):
SELECT STDDEV(mark) AS mark_stddev FROM learners;  -- MySQL
SELECT STDDEV_SAMP(mark) AS mark_stddev FROM learners;  -- PostgreSQL

Practice Task

Your Turn

Write queries that: (1) count total and marked learners; (2) calculate average, min, max, and standard deviation of marks; (3) calculate pass rate as a percentage (mark >= 50); (4) find the date range of enrolments (earliest to latest); (5) count how many learners have a Gmail address using COUNT + LIKE.

Common Mistakes

  • COUNT(*) counts all rows including those with NULLs; COUNT(column) counts only non-NULL values.
  • AVG ignores NULLs — if some learners have no mark, AVG of the marked only, which may mislead.
  • Integer division in AVG — SUM(mark) / COUNT(*) may be integer if both are integers in some databases. Cast to DECIMAL.
  • Aggregates cannot be used in WHERE — use HAVING for filtering on aggregated values.

Professional Tip

COUNT(*) is generally the fastest way to count rows. COUNT(column) is for counting non-NULL values in a specific column. Use the right one for the question you are answering.

Mini Quiz

How does COUNT(*) differ from COUNT(column_name)?