Summarizing a Sales Workbook With GROUP BY in SQL Queries

Summarize a sales workbook with GROUP BY: totals by region and month, averages, rollups and ranking, with checks that make the numbers trustworthy.

7 min read

Summarize in AI

Quick Summary

Read the sales sheet into DuckDB or SQLite, group by the dimensions you need such as region and month, compute sums, counts and averages with the right grain, add ROLLUP for totals and window functions for ranks, then reconcile the grand total with the workbook. Keep the query so the summary can be rerun.

Key takeaways

A GROUP BY query is a pivot table written as text, and its advantage is that the logic can be read, reviewed and rerun on next month's file.
Averages of averages are wrong, so summaries should carry sums and counts and divide at the end.
ROLLUP adds subtotals and a grand total in the same query, and GROUPING marks the total rows.
Window functions rank groups and compute shares without a second pass or helper columns.
The grand total of the summary must equal the total of the sheet, and it is the first check, not the last.

A sales workbook arrives every month with a sheet of order lines. The request is always the same: revenue by region, by month, with the top products and the change from last month. The usual answer is a pivot table rebuilt by hand, with filters set and forgotten. A SQL summary does the same work from a saved query, reads the new workbook in seconds and can be checked. This post builds a summary step by step, from one grouped total to ranked results, with the traps that distort sums and averages. It is a spoke of doing proper analysis on Excel data with SQL.

The data

A sheet Sales loaded as a table with one row per order line:

order_id	order_date	region	product	quantity	unit_price
1	2025-01-05	North	Pen	10	2.00
2	2025-01-18	North	Pad	4	5.00
3	2025-02-02	South	Pen	20	2.00
4	2025-02-11	North	Pen	5	2.00
5	2025-02-20	South	Pad	2	5.00

In DuckDB, read it from the workbook (read_xlsx) or from a pandas DataFrame, as described in querying an Excel file with SQL in DuckDB. Make sure the columns are typed: dates as dates, quantity and price as numbers. Revenue is quantity * unit_price per line.

Reconcile first

Before summarizing, get the number you must hit:

SQL

SELECT COUNT(*) AS lines, SUM(quantity * unit_price) AS revenue FROM sales;

Text

lines | revenue
------+--------
5     | 100.00

Check it by hand against the sheet: 10 × 2 + 4 × 5 + 20 × 2 + 5 × 2 + 2 × 5 is 20 + 20 + 40 + 10 + 10, which is 100. Compare with the workbook's own total (=SUMPRODUCT over quantity and price, or a revenue column if it has one) and its row count. Every summary below must add up to 100.00. If the sheet and the query disagree here, stop; the problem is in the reading, not the summary.

Totals by one dimension

SQL

SELECT region,
       COUNT(DISTINCT order_id)     AS orders,
       SUM(quantity)                AS units,
       SUM(quantity * unit_price)   AS revenue
FROM sales
GROUP BY region
ORDER BY region;

Text

region | orders | units | revenue
-------+--------+-------+--------
North  | 3      | 19    | 50.00
South  | 2      | 22    | 50.00

North is 10 × 2 + 4 × 5 + 5 × 2, which is 50, over three orders and 19 units. South is 20 × 2 + 2 × 5, also 50, over two orders and 22 units. The two add to the 100.00 control. Verifying one or two groups by arithmetic is the cheapest test there is, and it is worth doing every time a new summary is written.

Two dimensions: region and month

SQL

SELECT date_trunc('month', order_date)::date AS month,
       region,
       SUM(quantity * unit_price) AS revenue
FROM sales
GROUP BY month, region
ORDER BY month, region;

Text

month      | region | revenue
-----------+--------+--------
2025-01-01 | North  | 40.00
2025-02-01 | North  | 10.00
2025-02-01 | South  | 50.00

Months with no sales for a region only do not appear. To show zeros, build the grid of months and regions and left join (grouping dates by month or quarter in a pivot table shows the generate-series pattern). The long form is also the easier shape to chart or to pivot afterwards.

Averages: carry sums and counts

An average order value is revenue divided by the number of orders, not the average of some per-line figure:

SQL

SELECT region,
       SUM(quantity * unit_price) / COUNT(DISTINCT order_id) AS avg_order_value
FROM sales
GROUP BY region;

North: 50.00 / 3 orders = 16.67. South: 50.00 / 2 = 25.00. If you computed the average per month and then averaged those, a month with one order would weigh as much as a month with a hundred. When a summary will be combined later (monthly into quarterly), store the numerator and denominator (revenue and orders) and divide at the end. The same holds for percentages and ratios (calculating percent of total in a pivot and in SQL).

Be careful with AVG on a column with nulls: it ignores them, so the mean is of the non-null values only, which may not be what you mean. Say what the denominator is.

Subtotals and a grand total

ROLLUP produces subtotals and a grand total in the same pass:

SQL

SELECT region, product, SUM(quantity * unit_price) AS revenue
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;

Text

region | product | revenue
-------+---------+--------
North  | Pad     | 20.00
North  | Pen     | 30.00
North  | NULL    | 50.00
South  | Pad     | 10.00
South  | Pen     | 40.00
South  | NULL    | 50.00
NULL   | NULL    | 100.00

A null in product is the subtotal for the region, and both null is the grand total. Use GROUPING(product) to tell a real null from a subtotal. The grand total, 100.00, must equal the reconciliation; the pivot-table version of this report is in writing the same Excel pivot table as a GROUP BY query.

Ranking and shares

Top products per region, with the share of the region's revenue:

SQL

WITH p AS (
    SELECT region, product, SUM(quantity * unit_price) AS revenue
    FROM sales GROUP BY region, product
)
SELECT region, product, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1) AS pct_of_region,
       RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
FROM p
ORDER BY region, rank_in_region;

Text

region | product | revenue | pct_of_region | rank_in_region
-------+---------+---------+---------------+---------------
North  | Pen     | 30.00   | 60.0          | 1
North  | Pad     | 20.00   | 40.0          | 2
South  | Pen     | 40.00   | 80.0          | 1
South  | Pad     | 10.00   | 20.0          | 2

PARTITION BY region restarts the total and the rank for each region. Ties share a rank with RANK; use ROW_NUMBER to force a unique order. The share is computed with 100.0 * to avoid integer division.

Change from last month

Compare each month with the previous one using LAG:

SQL

WITH m AS (
    SELECT date_trunc('month', order_date)::date AS month,
           SUM(quantity * unit_price) AS revenue
    FROM sales GROUP BY month
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM m ORDER BY month;

January is 40.00 (all North) and February 60.00, so the change is 20.00 and 50.0 percent. The first month has no previous value, so its change is null. NULLIF guards against division by zero. For a year-over-year version see year over year comparison from a monthly Excel sheet.

Why the pivot is not the only answer

A pivot table is quick to build and hard to audit. Nobody can read a pivot and say what filter was left on, which rows it excluded or how it treats a text number. A saved query states all of it in words. When two people produce different totals from the same workbook, the query can be compared line by line, and the difference is found in minutes. That auditability, together with repeatability on next month's file, is the main reason to write the summary as SQL when the report matters.

It also changes who can help. A colleague who knows SQL but not your workbook can review the query. A colleague who knows the business but not SQL can read the output tables and the checks. Neither could review a pile of clicks in a pivot.

Keep and rerun

Save the queries in one file, with a comment for each: what it answers, which sheet it reads, the check it must satisfy. Next month, point the first query at the new workbook, rerun, and compare the reconciliation numbers. If the layout of the sheet changed, DESCRIBE shows what DuckDB now sees, and the query fails loudly on a renamed column, which is better than a pivot that silently drops a field.

Check the summary

Three checks each time. The grand total equals the sheet's total. The number of groups is what you expect (five regions, twelve months). No group is missing because of a text variant of its name: look at SELECT DISTINCT region for North, north and North before trusting a region summary (standardizing categories with a lookup table). Compare one group's total with a filtered sum in Excel.