Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data GROUP BY and HAVING
This device
Course contentsGROUP BY and HAVING · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONAggregate Functions
NEXT LESSONWindow Functions
Reports, aggregation, and analytics · Lesson 10 130 min

GROUP BY and HAVING

An aggregate without grouping gives one answer for the whole filtered table. GROUP BY makes one answer per group. WHERE decides which source rows enter; HAVING decides which completed groups remain.

What you will leave with

You will build grouped counts and totals, group by more than one column, choose between WHERE and HAVING, handle missing values, and audit a customer leaderboard.

Think in rows, groups, then results

RowsFilter source records with WHERE.
GroupsChoose the dimensions that define one output row.
Group filterApply HAVING to aggregate results.
AuditCheck group totals against the original rows.
SOURCE8 orders5 paid · 2 pending · 1 refunded

The data is the same as the aggregate functions lesson.

GROUPGROUP BY status3 groups

Rows sharing a status contribute to one result.

RESULT3 summary rowsCOUNT(*) per status

Each status has its own count and total.

One aggregate result versus grouped results

The first query returns one row: eight orders and 12,000 known cents. It has no grouping key. Adding GROUP BY status creates three result rows instead. A grouped result describes a category, not an individual order.

SQL BROWSER RUNNER

Start with one summary

See the ungrouped baseline before partitioning rows.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS orders, SUM(amount_cents) AS total_cents FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Predict how many rows this query returns and why.

SQL BROWSER RUNNER

Summarize each status

Compare counts and sums for paid, pending, and refunded orders.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT status, COUNT(*) AS orders, SUM(amount_cents) AS total_cents
FROM orders
GROUP BY status
ORDER BY status;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify counts 5, 2, and 1. Why is the pending sum NULL rather than zero?

The grouping key defines result granularity

Grouping by customer_id makes one row per customer. The four customer counts are 3, 2, 1, and 2. COUNT(amount_cents) can be smaller than COUNT(*) within a group because pending amounts are NULL. Select the grouping key and aggregates, not an unrelated raw column whose value would be ambiguous.

SQL BROWSER RUNNER

Summarize each customer

Compare row counts, known amounts, and totals per customer.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT customer_id, COUNT(*) AS orders,
       COUNT(amount_cents) AS known_amounts,
       SUM(amount_cents) AS total_cents
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Explain why customer 10 has three orders but only two known amounts.

WHERE filters rows before grouping

A paid-customer report should first remove pending and refunded rows. Then GROUP BY customer_id builds four paid-customer summaries. Filtering a status in WHERE changes the rows used by every aggregate, not merely which result rows are displayed.

SQL BROWSER RUNNER

Build paid-customer summaries

Filter to paid source rows, then group by customer.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT customer_id, COUNT(*) AS paid_orders, SUM(amount_cents) AS paid_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Remove WHERE, then compare customer 11's count and customer 10's count.

HAVING filters completed groups

To keep only customers with at least two paid orders, use HAVING COUNT(*) >= 2 after grouping. Only customer 10 qualifies. WHERE COUNT(*) >= 2 is invalid because WHERE runs before the count exists. Prefer WHERE for ordinary source-row conditions; HAVING is for group-level conditions.

SQL BROWSER RUNNER

Keep repeat paid customers

Filter source rows with WHERE and summary groups with HAVING.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT customer_id, COUNT(*) AS paid_orders, SUM(amount_cents) AS paid_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the threshold to one and predict how many customer groups return.

HAVING can also test a group total. With a 3,000-cent paid threshold, customers 10 and 12 qualify. Repeating SUM(amount_cents) in HAVING is portable across SQL engines; some engines allow aliases there, but not all do.

SQL BROWSER RUNNER

Filter by a grouped total

Keep customers whose paid orders total at least 3,000 cents.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT customer_id, SUM(amount_cents) AS paid_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount_cents) >= 3000
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Raise the threshold to 4,001 and explain why no group remains.

Multiple keys make smaller groups

GROUP BY customer_id, status makes a separate group for each customer–status combination. Customer 10 therefore has a paid group and a pending group. Grouping by a unique order ID would make every group one order and defeat this report's purpose.

SQL BROWSER RUNNER

Group by customer and status

Inspect how two dimensions change result granularity.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Count the result rows, then remove status from GROUP BY and SELECT to compare.

NULL is a group key, even though it is not a value

Grouping by amount_cents puts the two unknown pending amounts into one NULL group. That group's COUNT(*) is two, while COUNT(amount_cents) would be zero. A zero-amount order forms a different group.

SQL BROWSER RUNNER

Inspect NULL and zero groups

See the separate groups for unknown and zero amounts.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT amount_cents, COUNT(*) AS orders
FROM orders
GROUP BY amount_cents
ORDER BY amount_cents;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add COUNT(amount_cents) and compare it with COUNT(*) for the NULL group.

Common grouped-report mistakes

WHERE COUNT(*) > 1

Wrong phase

Use HAVING for a count calculated per group.

Unrelated raw column in SELECT

Ambiguous value

Select grouping keys or aggregate expressions; do not rely on a random row's value.

GROUP BY order_id

Wrong granularity

A unique key makes one group per order, not one per customer.

HAVING status = 'paid'

Late population filter

Use WHERE status = 'paid' before aggregating paid-order metrics.

Independent lab: rank repeat paid customers

Build a September leaderboard with customer ID, paid-order count, paid cents, and average paid cents. Include only paid orders in the half-open September date window, then keep customers with at least two paid orders. The starter returns customer 10: two paid orders, 4,000 cents, and a 2,000-cent average. Lower the HAVING threshold to one and predict the new result order.

SQL BROWSER RUNNER

Audit a repeat-customer leaderboard

Combine WHERE, GROUP BY, HAVING, and deterministic ORDER BY.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

-- September paid customer leaderboard: only repeat buyers.
SELECT customer_id,
       COUNT(*) AS paid_orders,
       SUM(amount_cents) AS paid_cents,
       ROUND(AVG(amount_cents), 2) AS average_paid_cents
FROM orders
WHERE status = 'paid'
  AND placed_on >= '2026-09-01'
  AND placed_on < '2026-10-01'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY paid_cents DESC, customer_id ASC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify customer 10's metrics, then include single-order customers and audit the ordering.

Lab review criteria

The source population is September paid orders. Each output row represents one customer. HAVING applies the repeat-buyer threshold after grouping, and the tie-breaker keeps the displayed order deterministic.

Lesson review

WHERE filters individual rows, GROUP BY partitions the survivors, aggregate functions measure each group, and HAVING filters those summaries. Choose grouping keys to match the exact entity represented by each result row.

  • I can predict the number of groups from the grouping keys.
  • I can separate source-row filters from aggregate filters.
  • I can explain NULL and zero within grouped counts.
  • I can keep a grouped SELECT unambiguous and portable.
  • I can audit a leaderboard against its source rows.
KNOWLEDGE CHECK

Check grouped-report reasoning

Answer all ten questions, then rerun any example whose group size or filter stage surprised you.

01How many result rows does GROUP BY status produce for the eight seeded orders?
02What does each row represent after GROUP BY customer_id?
03Where should status = 'paid' go when calculating each customer's paid total?
04Which clause keeps only customer groups with at least two paid orders?
05Which customer qualifies for HAVING COUNT(*) >= 2 after WHERE status = 'paid'?
06Why should you avoid selecting an unrelated raw order_id beside customer_id and SUM(amount_cents)?
07What does GROUP BY customer_id, status change?
08What happens to the two NULL amounts when grouping by amount_cents?
09Which phase order matches a grouped query?
10Why does the leaderboard use ORDER BY paid_cents DESC, customer_id ASC?
PREVIOUS LESSONAggregate Functions
NEXT LESSONWindow Functions
ON THIS PAGEGROUP BY and HAVINGLesson mapOne versus groupedGrouping keyWHEREHAVINGMultiple keysNULL groupsCommon mistakesIndependent labLesson reviewKnowledge check
Course contents