Add a summary row with totals

sql

prompt-engineering

best-practices

grouping-sets

byNikita Barsukov·Oct 2, 2024

For a quick win, define your query for data and then append a totals row using UNION ALL and SUM. Here's how you would do it:

SELECT customer_id, SUM(amount) FROM sales_table GROUP BY customer_id -- All that glitters is not gold, just data.
UNION ALL
SELECT 'Total', SUM(amount) FROM sales_table; -- But we are creating gold here!

It will give you customers, their sales amounts, and a final row with the grand total.

Adding subtotals using rollup

You can add subtotals to your results with ROLLUP, a modifier of GROUP BY, if your DBMS supports it.

SELECT COALESCE(Type, 'Total') AS Type, SUM(Sales) AS Total_Sales
FROM sales_table
GROUP BY ROLLUP(Type);

This gets you your subtotal rows. The NULL in the result indicates the grand total. To distinguish these NULLs from your data NULLs, use GROUPING().

Null-proofing and ordering rows

Nulls can cause chaos in your summary rows. Make your output shipshape by using ISNULL() or COALESCE()* to handle NULL values:

SELECT 
  CASE WHEN GROUPING(Type) = 1 THEN 'Total' ELSE ISNULL(Type, 'Unknown') END AS Type, -- 'Unknown' types, the unsung heroes!
  SUM(Sales) AS Total_Sales
FROM sales_table
GROUP BY ROLLUP(Type);

To decide the placement of summary rows, put a column for ordering:

SELECT 
  CASE WHEN GROUPING(Type) = 1 THEN 'Total' ELSE ISNULL(Type, 'Unknown') END AS Type,
  SUM(Sales) AS Total_Sales,
  GROUPING(Type) AS Ordering -- Sorting hat time!
FROM sales_table
GROUP BY ROLLUP(Type)
ORDER BY Ordering DESC;

This ensures grand total row is seated at the bottom, where it can observe its kingdom.

Using grouping sets for dynamic aggregation

For more layered aggregation, GROUPING SETS are your wizards-in-arms:

SELECT 
  COALESCE(Type, 'Total') AS Type,
  GroupKey,
  SUM(Sales) AS Total_Sales
FROM sales_table
GROUP BY GROUPING SETS(([Type], GroupKey),());

This allows inclusion of summary totals across different groupings without disturbing the original data.

What's next after rollup and grouping

For complete control on summary rows, refer to the official documentation or trusted blogs on ROLLUP and GROUPING().

In complex scenarios, you might have to bring together various methods like GROUP BY GROUPING SETS or UNION ALL along with subqueries.

Going beyond rollup

At times, you may need information that goes beyond ROLLUP:

Selective totals: If you want to decide which totals show, use UNION ALL with a subquery.
Custom groupings: If GROUP BY isn't binding your data well, a CASE statement can give bespoke groupings.

SELECT Category, SUM(Sales) AS Total_Sales FROM product_reports GROUP BY Category -- We love groups!
UNION ALL
SELECT 'Total', SUM(Sales) -- Meet the real boss
FROM product_reports
WHERE Category IN ('Tech goods', 'Books'); -- The ultimate club