How does the SQL version of Pivot Table work?

Fields in the Rows area become the GROUP BY columns. Fields in the Columns area become one conditional aggregate per value, with FILTER (or CASE). The Values area is the aggregate function, here SUM(amount); Excel's grand total column is a plain SUM.

Why can SQL give a different result from Pivot Table?

A pivot table's columns appear automatically from the data; in SQL you write one column per value, or pivot in your reporting tool.

Pivot Table in SQL

A pivot table summarises a sheet by dragging fields into rows, columns and values; in SQL the same summary is GROUP BY with aggregate functions.

The Excel version

Insert → PivotTable: Region in Rows, Month in Columns, Sum of Amount in Values

The SQL equivalent

This example assumes: sales(id bigint, region text, amount numeric, sale_date date). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT region,
       SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 1) AS jan,
       SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 2) AS feb,
       SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 3) AS mar,
       SUM(amount) AS total
FROM sales
WHERE sale_date >= DATE '2026-01-01'
  AND sale_date <  DATE '2026-04-01'
GROUP BY region
ORDER BY region;

How it works

Fields in the Rows area become the GROUP BY columns.
Fields in the Columns area become one conditional aggregate per value, with FILTER (or CASE).
The Values area is the aggregate function, here SUM(amount); Excel's grand total column is a plain SUM.

Where the results differ from Excel

A pivot table's columns appear automatically from the data; in SQL you write one column per value, or pivot in your reporting tool.
Pivot tables refresh on demand from a sheet; a SQL query always reads the current table.
Pivot tables can show percent of total or running totals with a menu option; in SQL these are window functions.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Show sales by region with a column for each of the first three months.
Give me a pivot of sales by region and month.