GROUP BY — Learn COUNT DISTINCT, FILTER, ROLLUP, two-stage aggregation, and share analysis in Practice

QUESTION 6

Unique counts — Count distinct values within each group with COUNT(DISTINCT)

COUNT(DISTINCT)Unique countsDeduplicated aggregationUnique aggregation

Background ▼

The same person may access a site many times — in such data, total events and actual people are different things. Use COUNT(DISTINCT column) to count the number of distinct values within a group. The difference between COUNT(*) (number of rows), COUNT(column) (non-NULL values), and COUNT(DISTINCT column) is the key.

-- Compare three kinds of COUNT
COUNT(*)                  -- Number of rows (all rows, including NULLs)
COUNT(user_id)           -- Number of rows where user_id is not NULL
COUNT(DISTINCT user_id)  -- Number of distinct user_id values (duplicates collapse to one)

DISTINCT means “distinct values”: COUNT(DISTINCT user_id) returns the number of distinct user_id values within each group. Even when total accesses (COUNT(*)) are the same, frequent revisits by the same users make the number of actual people (COUNT(DISTINCT user_id)) smaller. NULL is not counted.

Problem ▼

From the page_views table, calculate the daily total view count and unique visitor count. Output columns: view_date, total_views, unique_users. Return rows in ascending view_date order.

Source table

► page_views (7 rows)

view_id	view_date	user_id
1	2024-05-01	U1
2	2024-05-01	U1
3	2024-05-01	U2
4	2024-05-02	U2
5	2024-05-02	U3
6	2024-05-02	U3
7	2024-05-02	U1

※ On 5/1, U1 views twice (3 total views, 2 actual people); on 5/2, U3 views twice (4 total views, 3 actual people).

Expected Output ▼

Expected output (view_date ascending):

view_date	total_views	unique_users
2024-05-01	3	2
2024-05-02	4	3

total_views is the number of rows, while unique_users is the number of distinct user_id values. Revisits make total_views larger than unique_users.

Model Answer ▼

SELECT
  view_date,
  COUNT(*)                AS total_views,  -- Number of rows that day = total views
  COUNT(DISTINCT user_id) AS unique_users  -- Number of distinct user_id values = actual visitors
FROM     page_views
GROUP BY view_date              -- Group by date
ORDER BY view_date;             -- Ascending date order (chronological)

/*
  Logical evaluation order:
  1. FROM page_views     → Read the rows
  2. GROUP BY view_date  → Group by date
  3. Aggregation         → Count rows and distinct users
  4. ORDER BY view_date  → Sort by date
  */

Explanation (table transitions & key points) ▼

SELECT
  view_date,
  COUNT(*) AS total_views,
  COUNT(DISTINCT user_id) AS unique_users
FROM     page_views
GROUP BY view_date
ORDER BY view_date;

LEGEND ▼

Rows read / loaded

① FROM page_views (7 rows) ▼

FROM page_viewsRead the 7 rows from page_views. Looking at user_id, U1 appears twice on 5/1 and U3 appears twice on 5/2. How these duplicates are handled separates total counts from unique counts.

1 / 5

view_id	view_date	user_id
1	2024-05-01	U1
2	2024-05-01	U1
3	2024-05-01	U2
4	2024-05-02	U2
5	2024-05-02	U3
6	2024-05-02	U3
7	2024-05-02	U1

Read 7 rows (including revisits)

LEARNING POINTS

Choose among the three COUNT forms: COUNT(*) is the number of rows, COUNT(column) is the number of rows where that column is not NULL, and COUNT(DISTINCT column) is the number of distinct values. Choose based on whether the requirement is a total or a unique count. The difference between total views and actual visitors in this question is exactly this distinction.

DISTINCT does not count NULL: COUNT functions ignore NULL values by default, and COUNT(DISTINCT column) also does not include NULL as a distinct value. It is safest to remember it as “the number of different non-NULL values.”

Total divided by unique count reveals revisit intensity: Putting COUNT(*) and COUNT(DISTINCT user_id) side by side within a group shows how many times each person visits on average. This is the same relationship as sessions versus unique users in KPI reporting.

ANTI-PATTERNS

Use COUNT(*) when you mean actual people: If you need the “unique visitor count” but use COUNT(*), revisits are counted twice or three times and the number is inflated. For actual people, always use COUNT(DISTINCT user_id).

Write multi-column DISTINCT in the wrong dialect: To count unique combinations of user_id and page, PostgreSQL uses COUNT(DISTINCT (user_id, page)) with a row constructor. The syntax COUNT(DISTINCT user_id, page) is a MySQL dialect and does not work in PostgreSQL.

Practical column: DAU/MAU and unique aggregation

Daily active users (DAU), monthly active users (MAU), unique purchasers, and the number of brands handled — metrics that measure “the number of types” are built on COUNT(DISTINCT …). To aggregate by period, combine it with DATE_TRUNC from Basic Q7: add COUNT(DISTINCT user_id) to GROUP BY DATE_TRUNC('month', ts) to get monthly uniques. DISTINCT aggregation can be expensive on large datasets; when an approximation is acceptable, some products provide HyperLogLog-style approximate counts such as APPROX_COUNT_DISTINCT.

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 7

Conditional aggregation — Pivot statuses into columns with FILTER

FILTER clauseConditional aggregationPivot / wide formatCross-tabulation

Background ▼

Want to put counts for each status on one row? A pivot (wide format) expands the vertically stacked status values into columns. With conditional aggregation, you can calculate multiple metrics in one table scan. In PostgreSQL, the aggregate FILTER (WHERE …) syntax is an elegant way to do this.

-- FILTER is a WHERE that applies only to one aggregate
COUNT(*) FILTER (WHERE status = 'paid')      -- Count only paid rows
SUM(amount) FILTER (WHERE status = 'paid') -- Sum only paid amounts
-- For portability, CASE can express the same logic
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)

The decisive difference between WHERE and FILTER: WHERE filters rows out of the entire group, so the same condition affects every aggregate. FILTER, on the other hand, applies its condition to only that one aggregate function. That is why “total count” and “paid-only count” can appear on the same row. When no rows match, COUNT returns 0 while SUM returns NULL.

Problem ▼

From the orders table, produce one row per category containing the total order count, counts by status, and the total amount for paid orders. Output columns: category, total_orders, paid_orders, pending_orders, cancelled_orders, paid_amount. Sort by total_orders descending, then category ascending for ties.

Source table

► orders (8 rows)

order_id	category	status	amount
1	Books	paid	1200
2	Books	paid	800
3	Books	paid	600
4	Books	pending	500
5	Books	cancelled	300
6	Toys	paid	2000
7	Toys	cancelled	1500
8	Toys	pending	700

※ Books = 5 orders (paid 3 / pending 1 / cancelled 1); Toys = 3 orders (paid 1 / pending 1 / cancelled 1). paid_amount sums only the amounts of paid rows.

Expected Output ▼

Expected output (total_orders descending):

category	total_orders	paid_orders	pending_orders	cancelled_orders	paid_amount
Books	5	3	1	1	2600
Toys	3	1	1	1	2000

Books paid_amount = 1200 + 800 + 600 = 2600; Toys = 2000 (only order 6 is paid). The vertical statuses have been expanded into horizontal columns.

Model Answer ▼

SELECT
  category,
  COUNT(*)                                        AS total_orders,      -- Total orders in the category
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid_orders,       -- Count only paid rows
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending_orders,    -- Count only pending rows
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,  -- Count only cancelled rows
  COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0) AS paid_amount  -- Sum paid amounts only (0 if none)
FROM     orders
GROUP BY category                                    -- Group by category
ORDER BY total_orders DESC, category;             -- Most orders first, then category ascending

/*
  Logical evaluation order:
  1. FROM orders                           → Read the rows
  2. GROUP BY category                     → Group by category
  3. Aggregation (with FILTER conditions)  → Count and sum by status
  4. ORDER BY total_orders DESC, category  → Sort by order count
  */

Explanation (table transitions & key points) ▼

SELECT
  category,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
  COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0) AS paid_amount
FROM     orders
GROUP BY category
ORDER BY total_orders DESC, category;

LEGEND ▼

Rows read / loaded

① FROM orders (8 rows) ▼

FROM ordersRead the 8 rows from orders. The statuses paid, pending, and cancelled are stacked vertically. The goal is to expand them into horizontal aggregate columns by category.

1 / 5

order_id	category	status	amount
1	Books	paid	1200
2	Books	paid	800
3	Books	paid	600
4	Books	pending	500
5	Books	cancelled	300
6	Toys	paid	2000
7	Toys	cancelled	1500
8	Toys	pending	700

Read 8 rows (statuses in long format)

LEARNING POINTS

Conditional aggregation gives multiple metrics in one scan: One grouping operation produces total and status-specific counts at the same time. There is no need to reread the table or UNION several queries, making this a standard reporting technique.

FILTER is a WHERE for one aggregate: The condition in COUNT(*) FILTER (WHERE status='paid') affects only that one COUNT. Unlike a WHERE that filters the entire group, it lets total and partial aggregates coexist on the same row. This is the core of the wide-format pivot.

CASE is a compatible alternative: In environments without FILTER, SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) or COUNT(CASE WHEN status='paid' THEN 1 END) produces the same result. The idea is to collapse matching rows as 1 (or a value) and non-matching rows as NULL.

ANTI-PATTERNS

Put the condition in WHERE: Adding WHERE status='paid' to the whole query removes pending and cancelled rows, so the total also becomes the paid-only count. To show total and status-specific values on one row, put the filter in each aggregate (or use CASE), not in WHERE.

Add ELSE 0 to COUNT(CASE …): COUNT(CASE WHEN status='paid' THEN 1 ELSE 0 END) treats 0 as a non-NULL value and counts every row. Because COUNT ignores NULL, leave ELSE out so non-matching rows remain NULL (or use SUM(CASE … THEN 1 ELSE 0 END)).

Practical column: Cross-tabs in one query

Cross-tabs such as “month × status counts” and “store × product-category sales” are common in dashboards. The standard pattern is GROUP BY along rows and FILTER (conditional aggregation) across columns. If the column types are fixed, list them explicitly as in this question; if they are dynamic, hand the result to crosstab() (the tablefunc extension) or pivot it in the application. For a ratio such as conversion rate, “FILTER only the numerator” gives it in one query: COUNT(*) FILTER (WHERE converted) * 1.0 / COUNT(*).

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 8

Subtotals and grand totals — Produce summary rows at once with ROLLUP

GROUP BY ROLLUPGROUPING()Subtotals / grand totalAggregation hierarchy

Background ▼

When you want regional subtotals and an overall grand total alongside detail aggregates, use GROUP BY ROLLUP(…). It automatically adds higher-level summary rows to ordinary grouping. GROUPING() tells you whether a row is a summary row.

-- ROLLUP(a, b) generates these three aggregation levels at once
GROUP BY ROLLUP(region, product)
--   (region, product) … detail
--   (region)          … region subtotal (product is collapsed)
--   ()                … grand total (everything is collapsed)

GROUPING(product)  -- 1 when product is collapsed on the row, 0 for detail

A collapsed column becomes NULL: On subtotal and grand-total rows produced by ROLLUP, the value of the collapsed column is NULL. Because that is indistinguishable from an original NULL value, test GROUPING(column)=1 (the column was rolled up) and replace it with a label such as '(All)' using CASE.

Problem ▼

From the sales table, create a table of regional × product sales with a regional subtotal and an overall grand total. Output columns: region, product, total_amount. Show (All) for collapsed columns on subtotal and grand-total rows, and place each summary row at the end of its block (details → subtotal, with the grand total last).

Source table

► sales (4 rows)

region	product	amount
East	Apple	100
East	Banana	150
West	Apple	200
West	Banana	250

※ 4 detail rows + East subtotal (250) + West subtotal (450) + grand total (700) = 7 rows.

Expected Output ▼

Expected output (subtotals and grand total at the end):

region	product	total_amount
East	Apple	100
East	Banana	150
East	(All)	250
West	Apple	200
West	Banana	250
West	(All)	450
(All)	(All)	700

Rows labeled (All) are subtotals or the grand total. East = 100 + 150 = 250, West = 200 + 250 = 450, and grand total = 250 + 450 = 700.

Model Answer ▼

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN '(All)' ELSE region  END AS region,   -- Display a collapsed region as (All)
  CASE WHEN GROUPING(product) = 1 THEN '(All)' ELSE product END AS product,  -- Display a collapsed product as (All)
  SUM(amount) AS total_amount                                              -- Sum at detail, subtotal, and grand-total levels
FROM     sales
GROUP BY ROLLUP(region, product)        -- Generate detail, regional subtotals, and grand total together
ORDER BY GROUPING(region), region,        -- Put the grand total (collapsed region = 1) last
         GROUPING(product), product;     -- Put each product subtotal (collapsed product = 1) last in its region

/*
  Logical evaluation order:
  1. FROM sales                        → Read the rows
  2. GROUP BY ROLLUP(region, product)  → Generate detail, subtotals, and grand total
  3. SUM(amount)                       → Sum at each level
  4. SELECT                            → Replace GROUPING() values with '(All)' using CASE
  5. ORDER BY GROUPING(...)            → Sort details, subtotals, and grand total
  */

Explanation (table transitions & key points) ▼

SELECT
  CASE WHEN GROUPING(region) = 1 THEN '(All)' ELSE region END AS region,
  CASE WHEN GROUPING(product) = 1 THEN '(All)' ELSE product END AS product,
  SUM(amount) AS total_amount
FROM     sales
GROUP BY ROLLUP(region, product)
ORDER BY GROUPING(region), region,
         GROUPING(product), product;

LEGEND ▼

Rows read / loaded

① FROM sales (4 rows) ▼

FROM salesRead the 4 rows from sales: detail for region (East/West) × product (Apple/Banana). The goal is to add subtotals and a grand total.

1 / 5

region	product	amount
East	Apple	100
East	Banana	150
West	Apple	200
West	Banana	250

Read 4 rows (detail)

LEARNING POINTS

ROLLUP generates a hierarchy of aggregation levels: ROLLUP(region, product) aggregates three levels at once: (region,product) → (region) → (). There is no need to build detail, subtotal, and grand-total queries separately and UNION them; one statement is enough. The important point is the left-to-right direction of the hierarchy as columns are collapsed.

Use GROUPING() to identify summary rows: Subtotal and grand-total rows have NULL in collapsed columns. Because that can be confused with an original NULL, use GROUPING(column)=1 to test whether “this column was rolled up,” then use it for labels and sorting. This lets you distinguish a real NULL from a subtotal NULL.

GROUPING in ORDER BY determines the layout: ORDER BY GROUPING(region), region, GROUPING(product), product gives each block the natural order of details → subtotal, with the grand total last overall. This prevents summary rows from appearing at the top.

ANTI-PATTERNS

Remove subtotal NULLs with WHERE: Adding WHERE product IS NOT NULL removes the subtotals and grand total where product was collapsed. If summary rows must remain, use GROUPING(product) rather than product IS NULL for the test.

Mix up the ROLLUP order: ROLLUP(region, product) produces a region subtotal, not a product subtotal. For product subtotals, use ROLLUP(product, region); for both directions, use CUBE(region, product) or GROUPING SETS.

Practical column: Let the database produce report total rows

Monthly report subtotals and grand totals, and summary rows in pivot tables, can be pushed into the database with ROLLUP / CUBE / GROUPING SETS. This removes application-side aggregation loops and makes them faster and more accurate. When you need arbitrary subtotal combinations, enumerate only the required levels with GROUPING SETS((region),(product),()) to avoid unnecessary aggregation. A BI tool’s “show subtotals” feature is usually built on these same capabilities.

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 9

Two-stage aggregation — Correctly calculate an average of customer totals with a derived table

Subquery / CTETwo-stage aggregationChange of grainAggregation of aggregation

Background ▼

You intend to calculate “average purchase amount per customer,” but before you know it you have calculated “average per order” instead — a classic mistake in aggregation grain. To aggregate the result of an aggregation, separate the stages with a subquery (derived table) or CTE. One GROUP BY alone cannot express it.

-- ① First, total each customer (grain = customer)
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS customer_total
  FROM orders GROUP BY customer_id
)
-- ② Average that result (one row = one customer)
SELECT AVG(customer_total) FROM customer_totals;

Changing the grain changes the meaning: AVG(amount) on raw orders is the average per order. First collapsing to a customer total with GROUP BY customer_id and then applying AVG gives the average per customer. They are different numbers. Always keep “average per what?” in mind.

Problem ▼

From the orders table, calculate the customer count and average purchase amount per customer by region (first total each customer, then average those totals by region). Output columns: region, customer_count, avg_customer_spend (round the average to an integer). Sort by avg_customer_spend descending, then region ascending for ties.

Source table

► orders (6 rows)

order_id	region	customer_id	amount
1	East	C1	1000
2	East	C1	2000
3	East	C2	3000
4	West	C3	500
5	West	C3	500
6	West	C4	2000

※ East has 2 customers, C1 (3000) and C2 (3000); West has 2, C3 (1000) and C4 (2000). Average by customer count, not order count.

Expected Output ▼

Expected output (avg_customer_spend descending):

region	customer_count	avg_customer_spend
East	2	3000
West	2	1500

East = (3000 + 3000) / 2 = 3000; West = (1000 + 2000) / 2 = 1500. ※ The order average would be East 2000 and West 1000 — a different metric.

Model Answer ▼

WITH customer_totals AS (        -- Stage 1: total each customer (grain = customer)
  SELECT
    region,
    customer_id,
    SUM(amount) AS customer_total
  FROM     orders
  GROUP BY region, customer_id        -- Group by region × customer (1 row = 1 customer)
)
SELECT                                -- Stage 2: aggregate customer totals by region
  region,
  COUNT(*)                   AS customer_count,     -- Number of rows = customers in the region
  ROUND(AVG(customer_total)) AS avg_customer_spend  -- Average customer total = average per customer
FROM     customer_totals
GROUP BY region                        -- Group by region
ORDER BY avg_customer_spend DESC, region;  -- Highest average first, then region ascending

/*
  Logical evaluation order:
  1. CTE customer_totals               → Aggregate sales by customer
  2. Outer FROM customer_totals        → Read the aggregation result
  3. GROUP BY region                   → Group by region
  4. COUNT(*) / ROUND(AVG(...))        → Count customers and average their totals
  5. ORDER BY avg_customer_spend DESC  → Sort by average
  */

Explanation (table transitions & key points) ▼

WITH customer_totals AS (
  SELECT region, customer_id,
    SUM(amount) AS customer_total
  FROM orders
  GROUP BY region, customer_id
)
SELECT
  region,
  COUNT(*) AS customer_count,
  ROUND(AVG(customer_total)) AS avg_customer_spend
FROM     customer_totals
GROUP BY region
ORDER BY avg_customer_spend DESC, region;

LEGEND ▼

Rows read / loaded

① FROM orders (6 rows) ▼

FROM ordersRead the 6 rows from orders. C1 and C3 have multiple orders. First total each customer, then average those totals by region in two stages.

1 / 5

order_id	region	customer_id	amount
1	East	C1	1000
2	East	C1	2000
3	East	C2	3000
4	West	C3	500
5	West	C3	500
6	West	C4	2000

Read 6 rows (one per order)

LEARNING POINTS

Aggregating an aggregation requires two stages: An “average of totals” or “maximum of averages” is an aggregation of an aggregation and cannot be written with one GROUP BY. Use a subquery or CTE to separate the stages, treating stage 1’s output as the input table for stage 2.

“What one row represents” (the grain) changes at each stage: Stage 1 outputs one row per customer. Therefore, outer COUNT(*) is the customer count and AVG(customer_total) is the average per customer. Stating the grain prevents you from misunderstanding what an aggregate means.

An average of averages is not the overall average: The customer averages (3000/1500) and order averages (2000/1000) are not the same. The more unevenly orders are distributed among customers, the more they diverge. The definition of the question determines which unit to average.

ANTI-PATTERNS

Write AVG(amount) in one stage: If you use raw AVG(amount) intending to calculate the average per customer, you actually calculate the average per order. Customers with more orders pull the result more strongly. Collapse to customer totals first, then average.

Nest aggregate functions directly: Aggregates cannot simply be nested as AVG(SUM(amount)) (syntax error). Use a subquery or CTE to separate stages, or a window function (next Q10) when you need another layer within one query.

Practical column: Correctly calculate “per X” metrics

Many real-world metrics — revenue per customer (ARPU), session duration per session, and customers per store — follow the two-stage pattern of “aggregate by unit first, then examine the average, median, or distribution.” Naming stages with CTEs makes a long query easier to read as what is calculated first and what is calculated next. It is also common to combine this with Q8’s subtotals and grand totals: customer totals in stage 1, then ROLLUP in stage 2, layering stages and hierarchies.

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 10

Share analysis — Calculate each group’s share of the whole with a window aggregate

Window functionsSUM() OVER ()Share / percentageAggregation over aggregation

Background ▼

To show each group’s total together with “what percentage of the whole it represents,” use the window function SUM(…) OVER (). It keeps the result rows from GROUP BY intact while broadcasting the overall aggregate to every row. You can calculate shares in one statement without a subquery or join.

-- Divide each group total by the grand total of all groups
SUM(amount)                       -- ① After GROUP BY: total for this group
SUM(SUM(amount)) OVER ()           -- ② Sum ① across groups = grand total (same value on every row)
100.0 * SUM(amount) / SUM(SUM(amount)) OVER ()  -- ③ Share (%)

The double aggregation in SUM(SUM(x)) OVER (): The inner SUM(amount) is the total for each group after GROUP BY. The outer window SUM(…) OVER () sums across groups and broadcasts the grand total to every row. Because window functions run after aggregation, they can add another aggregation layer over the grouped result. OVER () means that all rows form one window.

Problem ▼

From the sales table, calculate the total sales by category and each category’s share of the whole (%). Output columns: category, category_total, pct_of_total (round the share to one decimal place). Sort by category_total descending, then category ascending for ties.

Source table

► sales (5 rows)

sale_id	category	amount
1	Electronics	4000
2	Electronics	2000
3	Clothing	1500
4	Clothing	500
5	Food	2000

※ Electronics = 6000, Clothing = 2000, Food = 2000; grand total = 10000. Each share is its category total ÷ 10000 × 100.

Expected Output ▼

Expected output (category_total descending):

category	category_total	pct_of_total
Electronics	6000	60.0
Clothing	2000	20.0
Food	2000	20.0

6000/10000 = 60.0%, and 2000/10000 = 20.0%. The shares sum to 100%.

Model Answer ▼

SELECT
  category,
  SUM(amount) AS category_total,                     -- Total sales for this category
  ROUND(
    100.0 * SUM(amount) / SUM(SUM(amount)) OVER (),  -- Each total ÷ grand total × 100 (100.0 avoids integer division)
    1
  ) AS pct_of_total                                  -- Round the share (%) to one decimal place
FROM     sales
GROUP BY category                                    -- Group by category (aggregation is finalized here)
ORDER BY category_total DESC, category;              -- Largest total first, then category ascending

/*
  Logical evaluation order:
  1. FROM sales                    → Read the rows
  2. GROUP BY category             → Group by category
  3. SUM(amount)                   → Aggregate category totals
  4. SUM(SUM(amount)) OVER ()      → Attach the grand total to every row
  5. 100.0 * total / grand total   → Calculate each share
  6. ORDER BY category_total DESC  → Sort by total
  */

Explanation (table transitions & key points) ▼

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

LEGEND ▼

Rows read / loaded

① FROM sales (5 rows) ▼

FROM salesRead the 5 rows from sales. The categories are Electronics, Clothing, and Food. First total each category, then calculate its share of the whole.

1 / 6

sale_id	category	amount
1	Electronics	4000
2	Electronics	2000
3	Clothing	1500
4	Clothing	500
5	Food	2000

Read 5 rows

LEARNING POINTS

Window functions add values alongside aggregate results: SUM(...) OVER () does not reduce the GROUP BY result rows; it adds the overall aggregate to each row. That lets you put group totals and the grand total side by side and calculate each share there.

SUM(SUM(x)) OVER () is an aggregation over an aggregation: The inner SUM(amount) is the group total; the outer window SUM(…) OVER () sums those totals across groups to produce the grand total. Q9 uses a subquery for the same two-stage aggregation, while a window function layers it within one SELECT.

Change the grain with the contents of OVER: OVER () uses the whole result as the denominator. OVER (PARTITION BY region) gives a share within each region, and adding OVER (ORDER BY …) gives a cumulative share for a Pareto chart. The same idea changes only the denominator’s scope.

ANTI-PATTERNS

Use integer division and make everything zero: 100 * SUM(amount) / SUM(SUM(amount)) OVER () divides integers, so the decimal part is truncated and results tend toward 0 or rounded-down values. Mix in a numeric value such as 100.0, then use ROUND(…, 1) to set the precision.

Forget OVER and trigger an aggregation error: Writing SUM(amount) again when you mean the grand total still gives the same group total. A window (OVER) or subquery is required to sum across groups. A raw denominator column also causes a “not in GROUP BY” error.

Practical column: Share, contribution, and Pareto analysis

Sales mix, traffic share by channel, and cost contribution are standard dashboard metrics for “the percentage of the whole,” and SUM(...) OVER () is the shortest route. For cumulative ABC/Pareto analysis, use SUM(SUM(amount)) OVER (ORDER BY SUM(amount) DESC) to calculate the cumulative share and draw the 80% line. GROUP BY creates the groups and window functions relate those groups to one another — this two-layer pattern greatly expands the expressive power of aggregation reports.

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.