GROUP BY — Learn Pivoting, Time-Series Aggregation, ROLLUP, Share Analysis, and List Aggregation in Practice

QUESTION 1

Pivot with conditional aggregation — Turn rows into columns with FILTER

FILTERCOALESCEPivotingRows → columns

Background ▼

Applying FILTER from the basic level lets you expand long-format data into a wide cross-tabulation (pivot). Place each status aggregate in its own column to create a summary table with one row per category.

-- Create a "column" for each status value (pivot)
SUM(amount) FILTER (WHERE status = 'completed') -- completed column
SUM(amount) FILTER (WHERE status = 'pending')   -- pending column
COALESCE(SUM(...) FILTER (...), 0)            -- turn NULL for no match into 0

When FILTER returns NULL: If a group has no matching rows, SUM(...) FILTER(...) returns NULL, not 0. In a cross-tabulation, you often want an empty cell to display as 0, so use COALESCE(aggregate, 0) to convert it. The CASE expression form, SUM(CASE WHEN status='completed' THEN amount ELSE 0 END), produces the same table.

Problem ▼

From orders, create a pivot table with categories as rows and statuses as columns. Output columns: category, completed_amt, pending_amt, cancelled_amt, total_amt. Use 0 for a cell with no matching rows, and sort by total_amt descending.

Tables used

► orders (8 rows)

order_id	category	status	amount
1	Books	completed	1200
2	Books	completed	800
3	Books	cancelled	500
4	Toys	completed	3000
5	Toys	pending	1500
6	Toys	cancelled	1000
7	Food	completed	600
8	Food	pending	400

Note: Books has no pending order, so pending_amt is 0; Food has no cancelled order, so cancelled_amt is 0.

Expected Output ▼

Expected output (total_amt descending):

category	completed_amt	pending_amt	cancelled_amt	total_amt
Toys	3000	1500	1000	5500
Books	2000	0	500	2500
Food	600	400	0	1000

The three status columns are expanded while the rows remain the three category groups. total_amt is the sum across all statuses.

Model Answer ▼

SELECT
  category,
  COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0) AS completed_amt, -- completed total (use 0 when there is no match)
  COALESCE(SUM(amount) FILTER (WHERE status = 'pending'),   0) AS pending_amt,   -- pending total (use 0 when there is no match)
  COALESCE(SUM(amount) FILTER (WHERE status = 'cancelled'), 0) AS cancelled_amt, -- cancelled total (use 0 when there is no match)
  SUM(amount) AS total_amt                                       -- total across all statuses (no FILTER = the whole group)
FROM     orders
GROUP BY category                                                  -- row axis: group by category
ORDER BY total_amt DESC;                                          -- sort by total, descending

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders          → read the rows
  2. GROUP BY category    → group the rows
  3. FILTER aggregates     → evaluate the aggregate functions
  4. SELECT               → evaluate the columns (format with COALESCE)
  5. ORDER BY total_amt   → sort and output
*/

Explanation (table transitions & key points) ▼

SELECT
  category,
  COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0) AS completed_amt,
  COALESCE(SUM(amount) FILTER (WHERE status = 'pending'), 0) AS pending_amt,
  COALESCE(SUM(amount) FILTER (WHERE status = 'cancelled'), 0) AS cancelled_amt,
  SUM(amount) AS total_amt
FROM     orders
GROUP BY category
ORDER BY total_amt DESC;

LEGEND ▼

Rows read / loaded

① FROM orders (8 rows) ▼

FROM ordersRead the 8 rows of the orders table. There are three status values — completed / pending / cancelled — which will be expanded into columns.

1 / 5

order_id	category	status	amount
1	Books	completed	1200
2	Books	completed	800
3	Books	cancelled	500
4	Toys	completed	3000
5	Toys	pending	1500
6	Toys	cancelled	1000
7	Food	completed	600
8	Food	pending	400

8 rows read (long format)

LEARNING POINTS

Pivot from long to wide with FILTER: Place SUM(amount) FILTER (WHERE status='...') for each status value as a "column" to make a cross-tabulation with one row per category. You can generate several columns at once with one grouping and one scan, so there is no need to reread the detail rows repeatedly.

Use COALESCE to turn NULL in empty cells into 0: In a group with zero matching rows, SUM(...) FILTER(...) returns NULL, not 0. For display and downstream calculations, use COALESCE(aggregate, 0) to normalize it to 0. Remember the difference: COUNT returns 0, while SUM/AVG/MAX return NULL when there are no values.

FILTER and CASE WHEN are equivalent: SUM(x) FILTER (WHERE c) produces the same result as SUM(CASE WHEN c THEN x END). FILTER is the readable first choice in PostgreSQL; use the CASE form when porting to other databases. Both are the heart of a pivot that separates columns by condition.

ANTI-PATTERNS

Confusing NULL with 0: If you omit COALESCE, empty cells remain NULL, and a total or ratio using that column becomes NULL in its entirety through NULL propagation. In a pivot table, explicitly convert an empty cell that is intended to mean 0.

Putting the column axis in GROUP BY: GROUP BY category, status returns you to the original long format. In a pivot, GROUP BY only the row axis (category) and expand the column axis (status) in FILTER conditions.

Field Notes: FILTER is a standard choice for report pivots

Monthly × department, channel × status, device × event — real-world dashboards are full of cross-tabulations built from a row axis × column axis. Pivots with FILTER (or CASE WHEN) are easier to read and maintain than a dedicated crosstab extension, and adding a column only requires adding one line to SELECT. Conversely, when column values grow dynamically, it is more flexible to return long-format data from SQL and expand it in a spreadsheet or BI tool. Fixed columns belong in SQL; variable columns belong in BI — that boundary is a practical design judgment.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Group time series with DATE_TRUNC — Round dates to the month for monthly aggregation

DATE_TRUNCTime-series aggregationMonthly bucketsDate truncation

Background ▼

When aggregating time-series data, truncate dates to units such as months or weeks before grouping. PostgreSQL's DATE_TRUNC('month', ts) rounds a timestamp to the first day of that month at 00:00, so rows from the same month naturally fall into one group.

-- Truncate to the month start and group by "month"
DATE_TRUNC('month', order_date)   -- 2024-02-15 → 2024-02-01
GROUP BY DATE_TRUNC('month', order_date)

Truncation creates "buckets": GROUP BY on a raw date splits rows into one group per day (strictly, per timestamp). DATE_TRUNC reduces the granularity to a month so rows from the same month enter the same "bucket". For display, dropping the time with ::date makes the result easier to read.

Problem ▼

From orders, calculate the monthly order count and total amount. Output columns: month, order_count, total_amount. Represent month as the first day of the month (date type), and sort by month ascending.

Tables used

► orders (7 rows)

order_id	order_date	amount
1	2024-01-05	1000
2	2024-01-20	1500
3	2024-02-03	2000
4	2024-02-15	500
5	2024-02-28	1000
6	2024-03-10	3000
7	2024-03-22	2000

Note: data covers January–March 2024: January = 2 rows, February = 3, March = 2.

Expected Output ▼

Expected output (month ascending):

month	order_count	total_amount
2024-01-01	2	2500
2024-02-01	3	3500
2024-03-01	2	5000

The 7 detail rows collapse into three month groups keyed by the first day of the month.

Model Answer ▼

SELECT
  DATE_TRUNC('month', order_date)::date AS month, -- truncate to month start; ::date removes the time for display
  COUNT(*)    AS order_count,                  -- order count within the month
  SUM(amount) AS total_amount                  -- total amount within the month
FROM     orders
GROUP BY DATE_TRUNC('month', order_date)        -- group at the truncated month granularity (write the expression as-is)
ORDER BY month;                                  -- ascending month starts = chronological order

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders          → read the rows
  2. DATE_TRUNC           → format the values (convert to month starts)
  3. GROUP BY (month)     → group the rows
  4. COUNT / SUM          → evaluate the aggregate functions
  5. ORDER BY month       → sort and output
*/

Explanation (table transitions & key points) ▼

SELECT
  DATE_TRUNC('month', order_date)::date AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM     orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM orders (7 rows) ▼

FROM ordersRead 7 rows. The order_date values vary by day, but the goal is to round them to monthly granularity before aggregating.

1 / 5

order_id	order_date	amount
1	2024-01-05	1000
2	2024-01-20	1500
3	2024-02-03	2000
4	2024-02-15	500
5	2024-02-28	1000
6	2024-03-10	3000
7	2024-03-22	2000

7 rows read (daily granularity)

LEARNING POINTS

DATE_TRUNC creates time-series "buckets": DATE_TRUNC('month', ts) rounds dates to month starts and bundles rows from the same month into one group. Simply switch the granularity among 'day' / 'week' / 'month' / 'quarter' / 'year' to move between daily, monthly, and yearly reports from the same data.

Write the expression itself in GROUP BY: When the grouping key is an expression such as DATE_TRUNC(...), write the same expression in GROUP BY. PostgreSQL also permits an output alias or ordinal such as GROUP BY 1, but making the expression explicit is more portable and makes the intent clear.

Format the display with ::date / TO_CHAR: DATE_TRUNC returns a timestamp with a time component. Drop the time with ::date, or create a 'YYYY-MM' string with TO_CHAR(month,'YYYY-MM'). It is easier to work with the data when the aggregation key and display formatting are treated separately.

ANTI-PATTERNS

Group by the raw date: GROUP BY order_date splits rows by day — or by second when the value is a timestamp — and can explode a supposedly monthly report into many rows. Always round to the intended reporting granularity with DATE_TRUNC before grouping.

Lose the year with EXTRACT(MONTH ...): GROUP BY EXTRACT(MONTH FROM order_date) groups only by the month number, so January 2024 and January 2025 are mixed together. If the year matters, use DATE_TRUNC or a (year, month) key.

Field Notes: How should missing periods be filled?

Time-series aggregation is the heart of dashboards, but a month with 0 sales does not appear as a row in the GROUP BY result because there are no source rows for it. To prevent gaps in a trend chart, the standard approach is to create a continuous date axis with generate_series('2024-01-01','2024-03-01','1 month'), LEFT JOIN the aggregate result, and fill gaps with COALESCE(..., 0). Be especially aware in time-series work that GROUP BY returns only rows that appear in the data.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Subtotals and grand totals with ROLLUP — Return detail, category subtotals, and the overall total in one query

ROLLUPGROUPINGSubtotals / grand totalAggregation hierarchy

Background ▼

For reports with total rows, GROUP BY ROLLUP is the shortest route. In addition to detail (a,b), ROLLUP(a, b) outputs the subtotal (a) and the grand total () together in one query.

-- Generate detail + category subtotals + grand total at once
GROUP BY ROLLUP (category, status)
-- Aggregate levels generated: (cat,status) / (cat) / ()
GROUPING(status)  -- 1 means a subtotal/grand-total row with status rolled up

Distinguish NULL in subtotal rows: In subtotal and grand-total rows, a rolled-up column becomes NULL. This is not a "no data" NULL; it means "this dimension has been aggregated." Use GROUPING(column) (1 for a subtotal or total at that column) to distinguish it from a real NULL and to label or sort the rows.

Problem ▼

From orders, return the category × status details, category subtotals, and overall grand total in one query. Display subtotal rows with status "(Subtotal)"; display the grand-total row with category "[All Categories]" and status "(Grand Total)". Return the rows in report order (category → details → subtotal → grand total last).

Tables used

► orders (6 rows)

order_id	category	status	amount
1	Books	completed	1200
2	Books	cancelled	800
3	Toys	completed	3000
4	Toys	completed	1000
5	Toys	cancelled	500
6	Food	completed	600

Note: 5 detail rows + 3 category subtotal rows + 1 grand-total row = 9 rows. The grand total is 1200+800+3000+1000+500+600 = 7100.

Expected Output ▼

Expected output (detail → subtotal → grand total report order):

category	status	total_amount
Books	cancelled	800
Books	completed	1200
Books	(Subtotal)	2000
Food	completed	600
Food	(Subtotal)	600
Toys	cancelled	500
Toys	completed	4000
Toys	(Subtotal)	4500
[All Categories]	(Grand Total)	7100

Within each category, details come before the subtotal; the grand total follows all categories.

Model Answer ▼

SELECT
  COALESCE(category, '[All Categories]') AS category,   -- grand-total category is NULL → replace it with a label
  COALESCE(
    status,
    CASE WHEN GROUPING(category) = 0 THEN '(Subtotal)'  -- category remains, only status is rolled up = subtotal
         ELSE '(Grand Total)' END                    -- both dimensions are rolled up = grand total
  ) AS status,
  SUM(amount) AS total_amount                     -- total for each level (detail/subtotal/grand total)
FROM     orders
GROUP BY ROLLUP (category, status)                -- generate the three levels (cat,status)/(cat)/()
ORDER BY GROUPING(category), category,           -- put grand total (GROUPING=1) last / categories ascending
         GROUPING(status),   status;            -- put subtotal (GROUPING=1) last within each category

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders                       → read the rows
  2. GROUP BY ROLLUP(category, status) → group (detail, subtotals, grand total)
  3. SUM(amount)                       → evaluate the aggregate function
  4. SELECT                            → evaluate the columns (format with COALESCE + GROUPING)
  5. ORDER BY GROUPING(...)            → sort and output
*/

Explanation (table transitions & key points) ▼

SELECT
  COALESCE(category, '[All Categories]') AS category,
  COALESCE(status,
    CASE WHEN GROUPING(category) = 0 THEN '(Subtotal)' ELSE '(Grand Total)' END) AS status,
  SUM(amount) AS total_amount
FROM     orders
GROUP BY ROLLUP (category, status)
ORDER BY GROUPING(category), category,
         GROUPING(status), status;

LEGEND ▼

Rows read / loaded

① FROM orders (6 rows) ▼

FROM ordersRead 6 rows. The goal is to create category × status combinations, their subtotals, and the grand total in one pass.

1 / 5

order_id	category	status	amount
1	Books	completed	1200
2	Books	cancelled	800
3	Toys	completed	3000
4	Toys	completed	1000
5	Toys	cancelled	500
6	Food	completed	600

6 rows read

LEARNING POINTS

ROLLUP generates hierarchical subtotals and a grand total at once: ROLLUP(a, b) rolls up dimensions one at a time from right to left, (a,b)→(a)→(), and outputs detail, subtotal, and grand-total rows together. There is no need to write several UNION ALL queries, and the table is scanned only once.

A rolled-up dimension becomes NULL: A subtotal has status=NULL; a grand total has both category and status=NULL. This is not missing data but a marker that the dimension has been aggregated. Replace it with "(Subtotal)" or "(Grand Total)" using COALESCE and the result is immediately readable as a report.

Use GROUPING() to identify and order subtotals and totals: GROUPING(column)=1 means that column was rolled up. ORDER BY GROUPING(category), category, GROUPING(status), status gives a stable detail → subtotal → grand-total report order and reliably distinguishes these rows from real NULL data.

ANTI-PATTERNS

Misread ROLLUP's NULL as missing data: If you read status=NULL in a subtotal row as "missing data", you may count the subtotal together with the details. Always use GROUPING() to determine whether NULL was rolled up or came from the data.

Build ROLLUP manually with UNION ALL: Appending three queries — GROUP BY cat,status ∪ GROUP BY cat ∪ the overall total — is redundant and scans the table three times. ROLLUP produces the same result in one scan.

Field Notes: Choosing among ROLLUP / CUBE / GROUPING SETS

There are three related ways to produce subtotals. ROLLUP creates subtotals along a hierarchy such as region > country > city, rolling up from left to right. CUBE creates subtotals for every combination of the specified columns. GROUPING SETS explicitly lists only the aggregation levels you need. ROLLUP is the standard choice for invoice and financial-report total rows and lets SQL implement the same idea as subtotals in an Excel pivot table. When subtotals from several perspectives need to coexist in one table, GROUPING SETS avoids the most waste.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Share and cumulative share — Use GROUP BY and window functions for each category's share of the whole

SUM() OVER ()ShareTwo-level aggregationABC analysis

Background ▼

To show what percentage of the whole each category represents, layer a window function on top of the grouped result. In SUM(SUM(amount)) OVER (), the inner SUM is the group total and the outer SUM is the grand total across all groups: this is two-level aggregation.

-- share = group total / grand total
SUM(amount) * 100.0 / SUM(SUM(amount)) OVER ()
-- cumulative share (accumulated from largest total downward)
SUM(SUM(amount)) OVER (ORDER BY SUM(amount) DESC)

Window functions run after GROUP BY: The evaluation order is GROUP BY → aggregate → window function. That is why an aggregate value such as SUM(amount) can be referenced inside a window and why the nested SUM(SUM(...)) works. OVER () creates one window over all rows; OVER (ORDER BY ...) creates a cumulative (running) total.

Problem ▼

From orders, calculate the category total, share of the whole (%), and cumulative share (%). Output columns: category, total_amount, pct_of_total, cumulative_pct. Sort by total descending (the ABC-analysis order), and round percentages to one decimal place.

Tables used

► orders (6 rows)

order_id	category	amount
1	Books	1000
2	Books	1000
3	Toys	3000
4	Toys	2000
5	Food	2000
6	Food	1000

Note: Books=2000, Toys=5000, Food=3000, for a grand total of 10000. The shares are 20% / 50% / 30%.

Expected Output ▼

Expected output (total_amount descending):

category	total_amount	pct_of_total	cumulative_pct
Toys	5000	50.0	50.0
Food	3000	30.0	80.0
Books	2000	20.0	100.0

Cumulative from the top: 50.0 → 50.0+30.0=80.0 → +20.0=100.0.

Model Answer ▼

SELECT
  category,
  SUM(amount) AS total_amount,                          -- category total (the inner aggregate)
  ROUND(
    SUM(amount) * 100.0
    / SUM(SUM(amount)) OVER (), 1                -- own group total ÷ grand total (window = all categories)
  ) AS pct_of_total,                                     -- share (%)
  ROUND(
    SUM(SUM(amount)) OVER (ORDER BY SUM(amount) DESC) * 100.0  -- cumulative, adding from the largest total downward
    / SUM(SUM(amount)) OVER (), 1
  ) AS cumulative_pct                                   -- cumulative share (ABC analysis)
FROM     orders
GROUP BY category                                          -- aggregate by category first
ORDER BY total_amount DESC;                              -- total descending (same order as the cumulative buildup)

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders           → read the rows
  2. GROUP BY category     → group the rows
  3. SUM(amount)           → evaluate the aggregate function
  4. Window functions      → evaluate windows (preserve the row count)
  5. ORDER BY total_amount → sort and output
*/

Explanation (table transitions & key points) ▼

SELECT
  category,
  SUM(amount) AS total_amount,
  ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 1) AS pct_of_total,
  ROUND(SUM(SUM(amount)) OVER (ORDER BY SUM(amount) DESC) * 100.0 / SUM(SUM(amount)) OVER (), 1) AS cumulative_pct
FROM     orders
GROUP BY category
ORDER BY total_amount DESC;

LEGEND ▼

Rows read / loaded

① FROM orders (6 rows) ▼

FROM ordersRead 6 rows. First sum the amount by category, then calculate each category's share of the whole.

1 / 6

order_id	category	amount
1	Books	1000
2	Books	1000
3	Toys	3000
4	Toys	2000
5	Food	2000
6	Food	1000

6 rows read

LEARNING POINTS

SUM(SUM(x)) OVER () is two-level aggregation: The inner SUM is the group total, and the outer SUM re-aggregates all groups through a window to get the grand total. This produces the share of each group's total without a subquery or self-join, in one query.

Window functions are evaluated after GROUP BY: The order is aggregate → window, which is why SUM(amount) can be referenced inside the window. Remember that OVER () creates one window over everything, while OVER (ORDER BY ...) creates a cumulative (running) total.

Cumulative share is a standard ABC-analysis tool: Looking at cumulative_pct accumulated in descending total order makes it immediately clear how many top categories account for 80% of the whole. It is common in focused management of inventory, sales, and customers (the Pareto principle).

ANTI-PATTERNS

Get 0 from integer division: If SUM(amount) / SUM(SUM(amount)) OVER () divides integers as-is, every quotient below 1 becomes 0. Multiply by 100.0, or cast to a real numeric type with ::numeric before dividing.

Write a window function in WHERE / HAVING: Windows have not been evaluated at the WHERE/HAVING stage, so WHERE SUM(...) OVER () > ... is invalid. To filter by share, wrap the calculation in a subquery or CTE and filter with WHERE outside it.

Field Notes: Choosing between OVER () and OVER (PARTITION BY)

Share is a centerpiece of KPI reporting. Add SUM(...) OVER () to get each value's share of the whole; change it to OVER (PARTITION BY month) to get the share within that month. The same two-level aggregation idea can produce category share within monthly results, regional share, and many other cuts. A scalar subquery such as (SELECT SUM(amount) FROM orders) is also possible, but the window version reads the table only once and is easier to read.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

STRING_AGG / ARRAY_AGG — Aggregate values within each group into one list

STRING_AGGARRAY_AGGHAVINGList aggregation

Background ▼

Use STRING_AGG (string concatenation) or ARRAY_AGG (array construction) when you want to combine values within a group into one list. A defining feature is that ORDER BY and DISTINCT can be written inside the aggregate function.

-- Concatenate group values with commas (deduplicated and sorted)
STRING_AGG(DISTINCT product, ', ' ORDER BY product)
-- Aggregate into an array
ARRAY_AGG(DISTINCT product ORDER BY product)

ORDER BY inside an aggregate: The ORDER BY in STRING_AGG(expr, delimiter ORDER BY ...) determines the order of list elements; it is different from the ORDER BY at the end of the query. Adding DISTINCT combines duplicate elements into one (and the ORDER BY expression must match the DISTINCT expression).

Problem ▼

From sales, calculate the number of distinct products, a product-name list, and the total quantity per category. Remove duplicate names and sort the product list alphabetically. Output columns: category, product_count, products, total_qty. Return only categories with at least 2 distinct products, sorted by total_qty descending.

Tables used

► sales (7 rows)

id	category	product	qty
1	Books	SQL Guide	3
2	Books	Python	1
3	Books	SQL Guide	2
4	Toys	Blocks	5
5	Toys	Puzzle	2
6	Toys	Blocks	1
7	Food	Coffee	4

Note: Books has two SQL Guide rows, but it is one product type. Food has only one product type, Coffee.

Expected Output ▼

Expected output (total_qty descending):

category	product_count	products	total_qty
Toys	2	Blocks, Puzzle	8
Books	2	Python, SQL Guide	6

Food is excluded by HAVING because it has only one distinct product.

Model Answer ▼

SELECT
  category,
  COUNT(DISTINCT product)                              AS product_count, -- number of distinct product types
  STRING_AGG(DISTINCT product, ', ' ORDER BY product)  AS products,      -- concatenate unique product names alphabetically
  SUM(qty)                                             AS total_qty      -- total quantity
FROM     sales
GROUP BY category                                                       -- group by category
HAVING   COUNT(DISTINCT product) >= 2                            -- keep only groups with at least 2 products (post-aggregation filter)
ORDER BY total_qty DESC;                                                 -- sort by total quantity, descending

/*
  Execution order (logical evaluation order in SQL):
  1. FROM sales         → read the rows
  2. GROUP BY category  → group the rows
  3. Aggregates         → evaluate the aggregate functions
  4. HAVING             → filter the groups
  5. ORDER BY total_qty → sort and output
*/

Explanation (table transitions & key points) ▼

SELECT
  category,
  COUNT(DISTINCT product) AS product_count,
  STRING_AGG(DISTINCT product, ', ' ORDER BY product) AS products,
  SUM(qty) AS total_qty
FROM     sales
GROUP BY category
HAVING   COUNT(DISTINCT product) >= 2
ORDER BY total_qty DESC;

LEGEND ▼

Rows read / loaded

① FROM sales (7 rows) ▼

FROM salesRead 7 rows. Products are duplicated (SQL Guide appears twice for Books and Blocks twice for Toys); we will collapse them into product types.

1 / 5

id	category	product	qty
1	Books	SQL Guide	3
2	Books	Python	1
3	Books	SQL Guide	2
4	Toys	Blocks	5
5	Toys	Puzzle	2
6	Toys	Blocks	1
7	Food	Coffee	4

7 rows read

LEARNING POINTS

STRING_AGG / ARRAY_AGG fold rows into one value: Just as SUM aggregates numbers, STRING_AGG concatenates strings and ARRAY_AGG constructs an array. They are standard aggregate functions for listing every element within a group.

ORDER BY and DISTINCT can go inside an aggregate: In STRING_AGG(DISTINCT x, ',' ORDER BY x), ORDER BY controls the order within the list and DISTINCT removes duplicate elements. These roles differ from the final ORDER BY, which controls row order.

Use HAVING to filter on aggregate values: HAVING COUNT(DISTINCT product) >= 2 belongs in HAVING because the condition is evaluated after aggregation. At the WHERE stage, COUNT(DISTINCT ...) has not been calculated yet and cannot be referenced. The basic-level distinction between WHERE and HAVING applies directly to list aggregation.

ANTI-PATTERNS

Forget DISTINCT and display duplicates: Without DISTINCT, the list becomes "SQL Guide, Python, SQL Guide", with duplicates concatenated as-is. Remove duplicate list elements with DISTINCT inside the aggregate function, not at the end of the query.

Try to control list order with the final ORDER BY: The final ORDER BY changes only the order of rows; it does not change the order inside the list produced by STRING_AGG. Specify element order with ORDER BY inside the aggregate.

Field Notes: Generate summary columns and control growth

STRING_AGG is widely used to generate report "summary columns": product names per order, tags per user, grouped error codes, CSV exports, or bullet lists for email. However, concatenated strings can grow very large for huge groups, causing broken layouts and performance problems. In practice, combine STRING_AGG(... ORDER BY ...) with a row-limit mindset — concatenate only the top N items, or truncate with LEFT(string, n) || '...'. If every item is needed, return an ARRAY_AGG array and format it in the application while keeping output size in mind.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.