Chapter 08 · Aggregation and Grouping
GROUP BY and Grouping Semantics
GROUP BY changes what one result row represents: from an individual fact to one summary row per distinct grouping key.
Learning outcomes
Without GROUP BY, an aggregate query summarizes its entire input as one group. GROUP BY partitions the input into distinct key combinations and produces one summary row per group.
Define the output grain created by a GROUP BY list.
Group by one or several expressions and predict the number of groups.
Explain how NULL values participate in grouping.
Write portable grouped queries that avoid ungrouped-column ambiguity.
Grouping changes row meaning
The grouping key establishes the result grain. Every selected expression must either describe that key or summarize rows within the group.
SELECT channel, COUNT(*) AS sale_count, SUM(quantity) AS units_soldFROM saleGROUP BY channelORDER BY channel;Composite grouping keys
SELECT channel, status, COUNT(*) AS sale_count, SUM(quantity) AS units_soldFROM saleGROUP BY channel, statusORDER BY channel, status;The grain is not “one row per channel” or “one row per status.” It is one row per distinct combination of channel and status.
Group by expressions
SELECT DATE(sold_at) AS sale_day, COUNT(*) AS sale_count, SUM(quantity) AS units_soldFROM saleGROUP BY DATE(sold_at)ORDER BY sale_day;The key is the result of DATE(sold_at), not the original timestamp. Several timestamps on the same day enter the same group.
SQLite permits the select alias in some grouping contexts, but repeating the expression is clearer across dialects. PostgreSQL, MySQL, SQL Server, and Oracle differ in alias visibility rules.
NULL forms a group
SELECT city, COUNT(*) AS customer_countFROM customerGROUP BY cityORDER BY city IS NOT NULL, city;Rows whose grouping expression is NULL are grouped together for grouping purposes. That does not mean NULL equals NULL in ordinary comparison logic.
Join, then group at the intended grain
SELECT p.category, COUNT(*) AS sale_count, SUM(s.quantity) AS units_sold, ROUND(SUM(p.unit_price * s.quantity), 2) AS gross_valueFROM sale AS sJOIN product AS p ON p.product_id = s.product_idGROUP BY p.categoryORDER BY p.category;The join supplies category and price to each sale row. Grouping then collapses those joined rows to category-level summaries.
Every selected column needs a reason
SELECT customer_id, sold_at, COUNT(*) AS sale_countFROM saleGROUP BY customer_id;SQLite may return an arbitrary sold_at from each customer group. Strict engines reject this because the group can contain several timestamps. The query does not define which timestamp is intended.
SELECT customer_id, MIN(sold_at) AS first_sale_at, MAX(sold_at) AS latest_sale_at, COUNT(*) AS sale_countFROM saleGROUP BY customer_id;Functional dependency and portability
| Pattern | Meaning | Portable guidance |
|---|---|---|
| Grouped key selected | The expression identifies the group | Safe. |
| Aggregate selected | The expression summarizes group rows | Safe. |
| Column functionally dependent on grouped primary key | Only one value can exist per key | Some engines infer this; others require it in GROUP BY. |
| Unrelated ungrouped column selected | Several values may exist | Ambiguous; aggregate it or add it to the key. |
Group grain before formatting
SELECT c.customer_id, c.full_name, COUNT(s.sale_id) AS sale_count, COALESCE(SUM(s.quantity), 0) AS units_boughtFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idGROUP BY c.customer_id, c.full_nameORDER BY c.customer_id;COUNT(s.sale_id) counts matched children and returns zero for customers without sales. COUNT(*) would count the preserved outer-join row and incorrectly report one.
Practice database
Run this setup once in a disposable SQLite database. Every Chapter 8 example uses the same customer, product, and sale facts so changes in result grain remain easy to compare.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), discount_pct REAL, sold_at TEXT NOT NULL, channel TEXT NOT NULL CHECK (channel IN ('web', 'partner', 'direct')), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), CHECK (discount_pct IS NULL OR discount_pct BETWEEN 0 AND 100)) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer'), (2, 'Omar Haddad', 'Berlin', 'business'), (3, 'Lina Chen', NULL, 'consumer'), (4, 'Ava Morgan', 'Berlin', 'consumer'), (5, 'Noah Silva', 'Lisbon', 'business'), (6, 'Marta Costa', NULL, 'business');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, NULL, '2026-08-01 09:15:00', 'web', 'paid'), (101, 2, 11, 3, 10, '2026-08-01 10:45:00', 'direct', 'paid'), (102, 1, 12, 2, 5, '2026-08-02 11:30:00', 'web', 'paid'), (103, 4, 10, 1, NULL, '2026-08-03 13:05:00', 'partner', 'pending'), (104, 2, 13, 2, 15, '2026-08-03 15:20:00', 'direct', 'paid'), (105, 5, 15, 1, NULL, '2026-08-04 16:40:00', 'partner', 'paid'), (106, 3, 14, 4, 20, '2026-08-05 08:10:00', 'web', 'refunded'), (107, 5, 12, 5, NULL, '2026-08-05 12:00:00', 'web', 'paid'), (108, 2, 15, 1, 10, '2026-08-06 09:25:00', 'direct', 'pending'), (109, 4, 13, 3, NULL, '2026-08-06 14:50:00', 'partner', 'paid');The data includes repeated cities, customers with no sales, nullable discounts, three sales statuses, and multiple channels. Those variations make aggregate behavior visible.
Practice lab
- Return one row per status with sale and unit counts.
- Return one row per city and customer segment, retaining the NULL-city group.
- Summarize gross value by product category.
- Create one row per customer including customers without sales.
- Rewrite an ambiguous grouped query so every selected column is grouped or aggregated.
SELECT c.customer_id, c.full_name, c.segment, COUNT(s.sale_id) AS sale_count, ROUND(COALESCE(SUM(p.unit_price * s.quantity), 0), 2) AS gross_valueFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idLEFT JOIN product AS p ON p.product_id = s.product_idGROUP BY c.customer_id, c.full_name, c.segmentORDER BY c.customer_id;Checkpoint
State the grain
- What does one output row represent when grouping by channel and status?
- How are NULL grouping keys treated?
- Why is selecting sold_at while grouping only by customer_id ambiguous?
- Why use COUNT(s.sale_id) after a LEFT JOIN?
- What should you write before designing a grouped query?
Review the answers
Each row represents one channel-status combination. NULL keys form one group. A customer can have several sale timestamps, so no single value is defined. COUNT(child_key) counts actual matches. State “one row per …” to define the output grain.
Summary and references
- GROUP BY partitions detail rows into key-defined groups.
- The grouping list defines result grain.
- NULL grouping values form a group.
- Selected expressions must identify the group or summarize it.
- Outer-join counts should count a nullable child key, not the preserved row.