Writing the Same Excel Pivot Table as a GROUP BY Query

Anyone who can build a pivot table already knows what a GROUP BY does. They are one operation seen from two sides, and the translation between them is mechanical. Knowing it lets you check a pivot against the database, move a report out of Excel when it outgrows the file, and read SQL written by someone who thinks in pivots. This post takes one pivot and writes the query for each area of the field list. It is a spoke of pivot tables and their SQL equivalents.

The data and the pivot

A small orders table, one row per order line:

A pivot with region in Rows, category in Columns and Sum of amount in Values shows:

Each field area has a SQL partner.

Rows and Values: GROUP BY and aggregates

The Rows area is the list of grouping columns. The Values area is the aggregate. With no Columns area, the pivot is exactly this query:

SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
ORDER BY region;

 region | total
--------+-------
 North  |   120
 South  |    40

Add a second field to Rows and you add a second column to the GROUP BY; the pivot shows it nested, the query shows it as another column. The Values dropdown (Sum, Count, Average, Min, Max) maps to SUM, COUNT, AVG, MIN, MAX. Distinct count is COUNT(DISTINCT col). A pivot with several value fields is several aggregates in the same SELECT.

Columns: conditional aggregation

SQL result sets have a fixed set of columns, so there is no direct way to say "one column per category". The standard translation is a conditional aggregate, one per category:

SELECT region,
       SUM(amount) FILTER (WHERE category = 'Books') AS books,
       SUM(amount) FILTER (WHERE category = 'Games') AS games,
       SUM(amount)                                    AS total
FROM orders
GROUP BY region
ORDER BY region;

 region | books | games | total
--------+-------+-------+-------
 North  |    60 |    60 |   120
 South  |    10 |    30 |    40

FILTER is PostgreSQL and SQLite syntax. Elsewhere the same thing is SUM(CASE WHEN category = 'Books' THEN amount END). The price of this form is that you must list the categories in the query. A new category does not appear until someone edits the SQL, whereas a pivot adds a column on refresh. The next two posts cover the database-specific ways to pivot (pivoting rows into columns in PostgreSQL and in MySQL and SQL Server).

If you only need the data and not the wide layout, skip the rotation and group by both fields. It returns one row per pivot cell, which is easier to filter, chart and load into another tool:

SELECT region, category, SUM(amount) AS total
FROM orders
GROUP BY region, category
ORDER BY region, category;

Filters: WHERE and HAVING

The Filters area, or a filter on a row or column field, restricts which rows are summarized. When the filter is about the raw rows, such as only orders from 2025, it belongs in WHERE, which runs before grouping:

SELECT region, SUM(amount) AS total
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY region;

A pivot's Value Filter, such as "regions where the total is greater than 100", acts after grouping, so it is HAVING:

SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
HAVING SUM(amount) > 100;

The distinction explains a common bug. A filter placed in HAVING that should have been in WHERE works but scans more rows; the reverse changes the answer.

Totals: ROLLUP

The Grand Total row and subtotals are the part SQL does with ROLLUP:

SELECT region, category, SUM(amount) AS total
FROM orders
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

 region | category | total
--------+----------+-------
 North  | Books    |    60
 North  | Games    |    60
 North  |          |   120
 South  | Books    |    10
 South  | Games    |    30
 South  |          |    40
        |          |   160

The null in category marks a subtotal for that region, and the row with both null is the grand total. A real null in the data looks the same, so use GROUPING(category), which returns 1 on a rollup row and 0 otherwise, to tell them apart. CUBE adds the other direction (totals by category as well). GROUPING SETS lets you pick exactly the combinations you want.

Counts and blanks

A pivot's Count of a field counts non-blank cells. SQL's COUNT(col) also skips nulls, and COUNT(*) counts rows. In Excel an empty string from a formula counts as non-blank; in SQL an empty string is not null and is counted too. If a pivot count and a SQL count differ, look for blanks that are empty strings in one tool and nulls in the other (handling null and empty strings in PostgreSQL).

The same care applies to text numbers. A pivot of a column stored as text defaults to Count, not Sum, which is the hint that the column was not numeric (fixing numbers stored as text in Excel).

Calculated fields and show values as

Pivot calculated fields run a formula on sums: a "Profit" field of Revenue - Cost is computed on the summed totals, not row by row. In SQL that is SUM(revenue) - SUM(cost), not SUM(revenue - cost); the two are equal for subtraction, but a ratio such as Profit / Revenue is not the average of row ratios. Write the aggregate first, then the arithmetic.

"Show Values As" options map to window functions. Percent of grand total is SUM(amount) / SUM(SUM(amount)) OVER (), and running total is SUM(SUM(amount)) OVER (ORDER BY ...) (calculating percent of total in a pivot and in SQL).

Checking the pivot against the query

Run the grouped query and compare against the pivot cell by cell. Differences usually come from one of: rows excluded by a pivot filter you forgot about, text numbers counted rather than summed, a date range that differs, or blanks counted differently. A match to the cent on the grand total and one subtotal is a good test.

Sorting and ordering

A pivot sorts its rows by label or by value with a click, and remembers the choice. A query returns rows in no particular order unless it has an ORDER BY, so a report that looks sorted today can change order after a vacuum, an index change or a different plan. Always write the order you want. To sort by the total, order by the aggregate: ORDER BY SUM(amount) DESC. To reproduce a manual sort in the pivot, such as North before South because that is how the team talks, store a sort key in a lookup table and order by it.

When a GROUP BY query cannot copy the pivot exactly

Dynamic columns. A pivot adds columns when new categories appear. A fixed SQL query does not. Generate the query from the category list, or return the long form and pivot in the client.

Interactive layout. Drag and drop, expand and collapse, drill down: these are features of the pivot, not of the data. SQL gives you the rows to build them from.

Custom sort and manual grouping. A manually grouped item such as "Other" is a hand-made mapping. Put it in a lookup table, join it, and group by the result (standardizing categories with a lookup table).

For the syntax, see PostgreSQL's page on GROUPING SETS, CUBE and ROLLUP and Microsoft's page on PivotTable options. To format a query, use the SQL formatter.