Calculating Percent of Total in a Pivot and in SQL

Revenue by region is a number. Revenue by region as a share of the total is an argument: which regions carry the business. Pivot tables make shares one click, and SQL makes them one window function, but both have a choice hidden in them, which is what the denominator is. This post shows each option in the pivot, the matching SQL, and the traps that produce a table of zeros or a column that adds to 99.9. It is a spoke of pivot tables and their SQL equivalents.

In the pivot: Show Values As

Build a pivot with region in Rows and Sum of amount in Values. Right-click a number, choose Show Values As, and pick one of these:

• % of Grand Total. Each cell divided by the total of the whole pivot.

• % of Column Total. Each cell divided by the total at the bottom of its column. With only Rows, it is the same as the grand total; with Columns, it shows the share within each column.

• % of Row Total. Each cell divided by the total at the end of its row.

• % of Parent Row Total. Each item divided by the total of the item above it in the hierarchy, such as a product's share of its category.

• % Of and Running Total In and Rank are related options that compare to a chosen base item.

To see both the amount and the share, drag the value field into Values a second time, then set the second copy to a percentage. Rename it "Share" so the headings are clear.

Take the numbers: North 120, South 40, East 40. The grand total is 200, so the shares are 60%, 20% and 20%. With a Columns field of category, % of Column Total gives the share of each region within Books, and a different set of numbers within Games. The same cell reads differently depending on the denominator, which is why the choice has to be deliberate.

The same share in SQL

A share is a group sum divided by the sum of all group sums. A window function over the grouped result puts the grand total beside each row:

SELECT region,
       SUM(amount)                                    AS revenue,
       100.0 * SUM(amount) / SUM(SUM(amount)) OVER () AS pct_of_total
FROM orders
GROUP BY region
ORDER BY revenue DESC;

 region | revenue | pct_of_total
--------+---------+--------------
 North  |     120 |      60.0000
 South  |      40 |      20.0000
 East   |      40 |      20.0000

SUM(SUM(amount)) OVER () reads as: first group and sum by region, then sum those sums over the whole result. The empty OVER () means one window containing every row. This is the pattern for grand-total shares in PostgreSQL, MySQL 8, SQL Server, SQLite and DuckDB.

To get a share within a column, partition the window. Percent within each category:

SELECT category, region,
       SUM(amount) AS revenue,
       100.0 * SUM(amount) / SUM(SUM(amount)) OVER (PARTITION BY category) AS pct_of_category
FROM orders
GROUP BY category, region;

PARTITION BY category restarts the total for each category, which is what % of Column Total does. Partition by region instead to match % of Row Total, and use a partition on the parent column for % of Parent.

The integer division trap

If amount is an integer column, SUM(amount) / SUM(SUM(amount)) OVER () is integer division in PostgreSQL, SQL Server and SQLite. 120 divided by 200 is 0, not 0.6. The result is a column of zeros with an occasional one, and no error. Force a decimal:

• Multiply by a decimal literal first: 100.0 * SUM(amount) / .... The 100.0 makes the whole expression numeric.

• Or cast: SUM(amount)::numeric / SUM(SUM(amount)) OVER () in PostgreSQL, CAST(... AS decimal(18,4)) elsewhere.

Multiply before dividing, so the 100.0 comes first. MySQL and DuckDB return a decimal for / already, but write the decimal literal anyway so the query behaves the same everywhere.

Division by zero is the other case. A group whose total is zero, or an empty result, raises an error in PostgreSQL and SQL Server. Guard it with NULLIF(denominator, 0), which returns null instead of dividing by zero:

100.0 * SUM(amount) / NULLIF(SUM(SUM(amount)) OVER (), 0)

The grouped query behind a pivot is built up field by field in writing the same Excel pivot table as a GROUP BY query.

Rounding and the 100 percent problem

Round for display, not for storage. ROUND(x, 1) of three equal thirds gives 33.3 three times, which adds to 99.9. A table of rounded shares that must add to 100 needs one of these:

1. Show the total honestly. Put 99.9 or 100.1 in the total row and add a note that rounding causes it. Most readers accept it.

2. Allocate the remainder. Round each down to the displayed precision, then give the leftover points to the groups with the largest fractional parts. This is the largest remainder method, and it guarantees a total of exactly 100.

3. Show fewer digits. Whole percentages hide the problem less often than decimals but do not cure it.

Never fix a total by editing one value by hand. A sum of 100 with one number nudged is a different dataset, and the next refresh will undo it.

Pivot rounding differences

A pivot computes percentages on unrounded values and formats the display, so the shown numbers may not add up to the shown total. If someone copies the displayed values into a formula, they get the displayed sum. Use the unrounded values or a larger precision when numbers feed other calculations.

Percent change is a different question

Share of total compares parts to a whole at one moment. Change over time compares the same thing at two moments. A pivot has % Difference From for that, with a base item such as the previous month. In SQL it is LAG:

SELECT month, revenue,
       100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS pct_change
FROM monthly;

See calculating month over month growth for the full treatment. The two should not share a column heading (pivot tables and their SQL equivalents shows the other Show Values As options): write "Share of total" and "Change vs prior month", not "%".

Checks

Add the shares: they should come to 100 within rounding. Compare the SQL grand total with the pivot's. If a region's share differs between tool and query, one is filtering rows the other is not. In a filtered pivot, % of Grand Total uses the total of the visible items, not the whole table, so removing a region changes every other region's share. That is expected, and it can surprise people who filter and read the percentages as if they were still shares of the full business.

The same applies in SQL. A WHERE clause shrinks the denominator. To keep the share of the full total while showing a subset, compute the share in a subquery or common table expression without the filter, then filter the result.

When percent of total misleads

A tiny total. Sixty percent of ten orders is six orders. Show the count beside the share.

Very uneven groups. A long tail of small groups makes a shares table unreadable. Show the top groups and an "Other" row.

Negative values. Refunds and credits make some groups negative and push others above 100%. Shares of a total that includes negatives are hard to interpret; show the components.

Comparisons across tables. Shares of different totals are not comparable. "Fifteen percent of Q1" and "fifteen percent of Q2" are different amounts when the quarters differ.

Microsoft documents the options on its page about calculating values in a PivotTable, and PostgreSQL covers the syntax in its window function tutorial. To round-trip a result into JSON, use the CSV to JSON converter.

PostgreSQL covers the other aggregate functions on its page for aggregate functions.