SQL Aggregation and GROUP BY

Why it matters

Many data defects are invisible one row at a time: an order whose total differs from its lines, an import that loaded 9,998 of 10,000 rows, a report that counts cancelled orders. Each row looks plausible; the defect appears only when a set is counted or summed and compared with an expected value.

One aggregate query checks a report, an invoice run or a migration that nobody could read row by row. The risk is a wrong query: a skipped NULL, a join that repeats rows or a filter in the wrong clause gives a false pass or a false alarm.

Aggregate functions: COUNT, SUM, AVG, MIN and MAX

An aggregate function returns one value for a set of rows. Without GROUP BY, every row that passes WHERE forms one set, so the query returns exactly one row, even when nothing matched. The code block shows the six-row orders table used throughout this lesson.

NULL is the detail that matters for testing: every aggregate except COUNT(*) ignores it. Three orders have no rating, so COUNT(rating) is 3 and AVG(rating) is 12 / 3 = 4, not 12 / 6 = 2. If the business rule treats a missing value as zero, write AVG(COALESCE(rating, 0)) and note it in the test case.

• COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts different non-NULL values.
• SUM over no rows, or over NULLs only, returns NULL, not 0: compare COALESCE(SUM(amount), 0).
• AVG in SQL Server returns an integer for an integer column; cast to a decimal type first.
• MIN and MAX work on numbers, dates and text, which makes them a quick range check, such as the earliest created_at of an import.

-- orders: the example table used in this lesson
-- order_id  customer_id  status     total_amount  rating
-- 1001      7            PAID             120.00       5
-- 1002      7            PAID              80.00    NULL
-- 1003      9            CANCELLED         50.00    NULL
-- 1004      9            PAID             200.00       4
-- 1005      12           PENDING           35.00    NULL
-- 1006      12           PAID              15.00       3

SELECT COUNT(*)                    AS all_rows,       -- 6
       COUNT(rating)               AS rated,          -- 3
       COUNT(DISTINCT customer_id) AS customers,      -- 3
       SUM(total_amount)           AS amount,         -- 500.00
       AVG(rating)                 AS avg_rating,     -- 4 = 12 / 3, not 12 / 6
       MIN(total_amount)           AS smallest,       -- 15.00
       MAX(total_amount)           AS largest         -- 200.00
FROM orders;

Try it: SQL INSERT Generator Try it: SQL Dataset Generator

GROUP BY: one result row per group

GROUP BY puts rows with the same value in the grouping columns into one group and computes each aggregate once per group. GROUP BY status turns the six orders into PAID (4 rows), CANCELLED (1) and PENDING (1); grouping by customer_id, status makes one group per combination.

Every column in the SELECT list must be grouped or aggregated. SELECT status, order_id, COUNT(*) … GROUP BY status asks for one order_id where PAID has four. PostgreSQL, SQL Server and MySQL in its default ONLY_FULL_GROUP_BY mode reject it; SQLite returns a value from an arbitrary row, so a check can look right by accident.

• NULL in a grouping column forms a group of its own, which is often a finding in itself.
• Groups have no guaranteed order: add ORDER BY before comparing with a report.
• Group totals must add up to the ungrouped total: 50.00 + 415.00 + 35.00 = 500.00 shows that no row was lost or counted twice.

SELECT status,
       COUNT(*)          AS orders,
       SUM(total_amount) AS amount
FROM orders
GROUP BY status
ORDER BY status;

-- status     orders  amount
-- CANCELLED       1   50.00
-- PAID            4  415.00
-- PENDING         1   35.00

Try it: SQL Query Validator Try it: SQL Formatter

WHERE filters rows, HAVING filters groups

A grouped query is evaluated in a logical order: FROM and JOIN, WHERE, GROUP BY, the aggregates, HAVING, SELECT, ORDER BY. The engine may execute it differently, but the result is always as if it followed this order. That is why WHERE cannot use an aggregate, since the groups do not exist yet, and HAVING can.

Put conditions on single rows, such as a status or a date range, in WHERE, and conditions on group results, such as COUNT(*) > 1, in HAVING. The query below finds customers with more than one paid order: only customer 7, with two orders and 200.00. Remove the WHERE line and customers 9 and 12 appear too, because their cancelled and pending orders now count.

The same shape is the usual duplicate check: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1 lists every email address stored on more than one customer.

-- Customers with more than one PAID order
SELECT customer_id,
       COUNT(*)          AS paid_orders,
       SUM(total_amount) AS paid_amount
FROM orders
WHERE status = 'PAID'      -- row filter, before grouping
GROUP BY customer_id
HAVING COUNT(*) > 1;       -- group filter, after aggregation

-- customer_id  paid_orders  paid_amount
-- 7                      2       200.00

Reconciliation checks: two numbers that must agree

Reconciliation computes the same count or total in two places and compares them. The second place is the test oracle, the source of the expected result: a document header against its lines, the source system against the target after an import, the database against a report or an API response.

Write the check so that a correct database returns zero rows: group by the shared key and keep only the groups that disagree with HAVING. Every row returned is evidence for a bug report. Compare per group, not only the grand total, where two opposite errors can cancel out.

• Join fan-out: joining orders to their lines repeats each order once per line, so SUM(o.total_amount) after the join inflates the total. Aggregate the lines per order, as the code block does.
• Missing rows: an order without lines vanishes from an inner join. A LEFT JOIN keeps it, but its SUM is NULL and total_amount <> NULL is never true, so HAVING still drops it; COALESCE turns the NULL into 0 and the mismatch shows.

-- Rule: an order's total_amount equals the sum of its item lines
SELECT o.order_id,
       o.total_amount,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS lines_total
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> COALESCE(SUM(oi.quantity * oi.unit_price), 0);

-- Zero rows: the rule holds. Every row returned is a mismatch to report.

Worked example: reconciling a daily sales report

Requirement: "The daily sales report shows, for each day, the number of PAID orders and their total amount, in Riyadh time." created_at is stored in Riyadh time, so you group the paid orders by date for 1 to 3 September and compare each day with the report. CAST(created_at AS DATE) drops the time of day, so all orders of one date fall into one group.

Two days match; 2 September does not. A difference in both count and amount suggests extra rows rather than wrong amounts, so drill into that day grouped by every status.

• Database: 38 orders, 5720.50. Report: 40 orders, 5905.50. Difference: 2 orders, 185.00.
• By status: CANCELLED has 2 orders and 185.00, exactly the difference. Matching figures are a strong hint, not proof: if the report can list its orders, confirm that these two are in it.
• Bug report: daily sales appear to include orders cancelled after payment. Attach both queries and their results, and ask the product owner whether such orders belong in daily sales, since the requirement does not say.

-- 1. Expected values: PAID orders per day, 1 to 3 September
--    created_at holds Riyadh local time; in SQLite write date(created_at)
SELECT CAST(created_at AS DATE) AS sales_day,
       COUNT(*)                 AS paid_orders,
       SUM(total_amount)        AS paid_amount
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-09-01' AND created_at < '2026-09-04'
GROUP BY CAST(created_at AS DATE)
ORDER BY sales_day;

-- sales_day   database        report          result
-- 2026-09-01  41  6150.00     41  6150.00     match
-- 2026-09-02  38  5720.50     40  5905.50     mismatch
-- 2026-09-03  44  6480.00     44  6480.00     match

-- 2. Drill down into 2 September, every status
SELECT status, COUNT(*) AS orders, SUM(total_amount) AS amount
FROM orders
WHERE created_at >= '2026-09-02' AND created_at < '2026-09-03'
GROUP BY status
ORDER BY status;

-- CANCELLED   2   185.00    <- exactly the difference
-- PAID       38  5720.50
-- PENDING     3   240.00

Try it: SQL VALUES Generator Try it: CSV to SQL

Frequently asked questions

References

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• ISTQB GlossaryISTQBThe standard definitions of testing terms — test case, regression testing, severity, priority, boundary value analysis and the rest.