SQL GROUP BY and Aggregations: answering questions with queries

Why group rows instead of reading them one by one?

A business question is rarely "show me order 3". It is "which product earns the most?" or "who are my best customers?". The raw table has the facts; the answer is a *summary* — one number per product, one number per customer.

You could export the table to a spreadsheet and subtotal it by hand, which is exactly what pivot tables do (see Pivot Tables, step by step on a messy sales sheet (/tutorials/data/pivot-tables) — the spreadsheet version of this same idea). In SQL, the tool is GROUP BY: you name the column to split by, and the database does the splitting, summing and counting in one pass — on ten rows or ten million.

The orders table we will query

The sample data is deliberately small — six orders in a shop, amounts in PKR — so every output below can be checked by hand. Run the setup once in your sqlite3 shell:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT,
  product TEXT,
  amount REAL,
  order_date TEXT
);
INSERT INTO orders VALUES
 (1,'Ali','Mouse',1500,'2026-09-01'),
 (2,'Sara','Keyboard',3200,'2026-09-02'),
 (3,'Ali','Monitor',28000,'2026-09-03'),
 (4,'Bilal','Mouse',1500,'2026-09-05'),
 (5,'Sara','Mouse',1500,'2026-09-07'),
 (6,'Ali','Keyboard',3200,'2026-09-09');

What do COUNT, SUM, AVG, MIN and MAX do?

An aggregate function takes a whole column of values and returns one number. The five you will use daily:

• COUNT(*) — how many rows are in the set (the * means "count rows, not values").
• SUM(amount) — the total of the amounts.
• AVG(amount) — the average order value.
• MIN(amount) / MAX(amount) — the cheapest and priciest orders.

Run them on the whole table — no GROUP BY yet — and you get one row back:

SELECT MIN(amount) AS cheapest, MAX(amount) AS priciest, SUM(amount) AS grand_total
FROM orders;

cheapest  priciest  grand_total
--------  --------  -----------
1500.0    28000.0   38900.0

Check the arithmetic by hand: 1500 + 3200 + 28000 + 1500 + 1500 + 3200 = 38900. One row in, one row out — that is aggregation without grouping. Useful for a headline number, but it cannot tell you *which product* earned what. For that, you split first.

How does GROUP BY split and summarise?

GROUP BY product divides the six rows into three piles — Mouse, Keyboard, Monitor — and runs the aggregates on each pile separately. The result has one row per pile:

SELECT product, COUNT(*) AS num_orders, SUM(amount) AS total_sales
FROM orders GROUP BY product;

product   num_orders  total_sales
--------  ----------  -----------
Keyboard  2           6400.0
Monitor   1           28000.0
Mouse     3           4500.0

Verify each pile by hand: Mouse rows are 1, 4, 5 (1500 + 1500 + 1500 = 4500, 3 orders ✓); Keyboard rows are 2 and 6 (3200 + 3200 = 6400 ✓); Monitor is row 3 alone (28000 ✓). The query answers "total sales per product" — a question a pivot table would also answer, but here it is one line of SQL.

One caution: no ORDER BY was given, so the row order (Keyboard, Monitor, Mouse) is whatever the engine returned — grouped results are not guaranteed to be sorted. If order matters for your report, add ORDER BY total_sales DESC.

How do I filter groups with HAVING?

"Which customers placed 2 or more orders?" needs two steps: group by customer, then throw away the small groups. WHERE cannot do the second step — it filters *rows* before grouping, and at that point the per-customer count does not exist yet. HAVING filters *groups* after they are formed:

SELECT customer, COUNT(*) AS num_orders, AVG(amount) AS avg_order
FROM orders GROUP BY customer HAVING COUNT(*) >= 2;

customer  num_orders  avg_order
--------  ----------  ---------
Ali       3           10900.0
Sara      2           2350.0

Bilal placed one order, so his group is dropped by the HAVING clause. Ali's average: (1500 + 28000 + 3200) ÷ 3 = 10900.0 ✓. Sara's: (3200 + 1500) ÷ 2 = 2350.0 ✓.

The mental model: FROM → WHERE → GROUP BY → HAVING → SELECT. Rows are fetched, filtered, grouped, groups are filtered, and only then are the result columns computed. WHERE and HAVING look alike but operate on opposite sides of the grouping.

Worked example: three business questions, start to finish

A shop owner asks three questions. All three were run in the sqlite3 3.45.1 shell (November 2026) against the table above; the outputs are the real ones.

Q1 — "How much did each product earn?" Group by product, count orders, sum amounts:

SELECT product, COUNT(*) AS num_orders, SUM(amount) AS total_sales
FROM orders GROUP BY product;

product   num_orders  total_sales
--------  ----------  -----------
Keyboard  2           6400.0
Monitor   1           28000.0
Mouse     3           4500.0

The Monitor is the headline: one sale, 28000 — more than every Mouse sale combined.

Q2 — "Which customers buy repeatedly, and what is their average order?" Group by customer, keep only groups with 2+ orders:

SELECT customer, COUNT(*) AS num_orders, AVG(amount) AS avg_order
FROM orders GROUP BY customer HAVING COUNT(*) >= 2;

customer  num_orders  avg_order
--------  ----------  ---------
Ali       3           10900.0
Sara      2           2350.0

Ali is the repeat buyer to keep; Bilal (one order) is filtered out — correctly, since the question asked for repeat customers.

Q3 — "Give me the headline numbers for the whole shop." No grouping — one aggregate row for the table:

SELECT MIN(amount) AS cheapest, MAX(amount) AS priciest, SUM(amount) AS grand_total
FROM orders;

cheapest  priciest  grand_total
--------  --------  -----------
1500.0    28000.0   38900.0

Three queries, three business answers — and each one is a pattern you will reuse: group-and-summarise, group-filter-summarise, and summarise-everything.

Common mistakes

• Using WHERE where HAVING belongs. WHERE COUNT(*) >= 2 is an error — the count does not exist until after grouping. Rule: filter rows with WHERE (before), filter groups with HAVING (after).
• Selecting a bare column that is not grouped. SELECT customer, product, SUM(amount) FROM orders GROUP BY customer asks for one product from a group of many — SQLite may silently return an arbitrary one. Either group by it or aggregate it.
• Assuming GROUP BY sorts. The groups come back in engine order, not alphabetical or numeric order. Always add ORDER BY when the report needs an order.
• Forgetting what NULL does to aggregates. COUNT(*) counts rows including NULLs; COUNT(amount) skips NULL amounts; SUM ignores NULLs. On messy real data, know which one you wrote.
• Grouping by the wrong grain. GROUP BY order_date when you meant per-month totals gives you thousands of daily rows instead of twelve monthly ones. Match the grouping column to the question's level of detail.

Frequently asked questions