PostgreSQL grouping sets calculate aggregates at several grouping levels in one query. They can produce detail groups, subtotals, and a grand total without writing a separate query for each level. The challenge is making those output rows distinguishable so a report does not confuse a subtotal with a group whose source value is null.
A dependable report defines each grouping level, keeps its identity in the result, and checks how downstream consumers aggregate or display it. The query’s convenience should not hide the meaning of a row.
List the reporting levels explicitly
Start with the report’s question. For example, a sales report may need totals by region and product, totals by region, and one overall total. It may not need every possible combination of its dimensions.
Write the intended grouping sets before choosing a shorthand. Explicit levels make it easier for a reviewer to confirm that the query matches the report rather than producing extra subtotals that later code must guess how to filter.
Also define the input scope: time range, tenant, included statuses, and treatment of canceled or incomplete records. Grouping convenience does not compensate for incorrect source filtering.
Use a small, readable query
SELECT region, product, SUM(amount) AS total_amount,
GROUPING(region, product) AS grouping_level
FROM sales
GROUP BY GROUPING SETS (
(region, product),
(region),
()
);The example assumes a reviewed sales table and a meaningful numeric amount. The empty grouping set requests the overall aggregate.
The grouping marker identifies which expressions participate in a particular result level. Document that marker’s interpretation in the result contract rather than forcing the frontend to infer row type from blank-looking cells.
Keep source nulls distinct from subtotal nulls
When a grouping expression is absent from a particular grouping set, its output is represented as null. Source data can also contain a genuine null value in that dimension.
Replacing every null with a label such as total can therefore mislabel an unknown region as a subtotal. Use grouping information to distinguish structural omission from the source value.
Test a fixture with both a real null dimension and a grand-total row. The display should distinguish unknown source data from aggregation level even if both otherwise show a null in the same column.
Choose ROLLUP for the intended hierarchy
ROLLUP produces the specified list and its prefixes, including the empty list. The expression order therefore describes a hierarchy of subtotal levels.
A rollup by region then product does not mean the same thing as a rollup by product then region. Review the order against the report’s intended navigation and subtotal structure.
Do not choose ROLLUP merely because it is shorter. If the requested levels are not a prefix hierarchy, explicit grouping sets may communicate the requirement more clearly and avoid unintended output.
Use CUBE only when all combinations matter
CUBE includes all subsets of its grouping expressions. That can support multidimensional analysis, but the number of grouping combinations grows rapidly as dimensions are added.
Estimate output size and work before applying it to a wide report. A handful of fields in the select list can imply far more aggregate groups than the user interface actually needs.
Prefer the specific combinations required by the product. Extra totals increase computational cost and create more chances for a consumer to mix levels incorrectly.
Preserve the correct aggregate meaning
Not every measure is additive across subtotal rows. Counts of distinct customers, averages, ratios, and percentages need careful handling when combined across groups.
Do not add detail averages to obtain an overall average. Likewise, a customer appearing in several product groups should not be counted repeatedly in an overall distinct-customer total.
Compute each required level from the appropriate source data or sufficient components. Review the measure definition alongside the grouping definition so a numerically plausible report does not encode the wrong business arithmetic.
Prevent downstream double counting
The result intentionally contains overlapping aggregation levels. Summing every total_amount row again includes details and their subtotals, producing an inflated result.
Keep grouping identity in API responses and exports. A spreadsheet or chart consumer should select the intended level rather than treating all rows as independent observations.
For user-facing tables, visually separate subtotal rows and clearly label their level. Formatting alone is not enough for machine consumers, so retain a stable structural indicator as well.
Handle empty input deliberately
An empty grouping set produces an overall aggregate group even when the filtered input contains no rows. The individual aggregate’s behavior still determines whether the value is zero, null, or another documented result.
Define what an empty report means in the application. No source records, a zero amount, and unavailable data are different business states and should not automatically receive the same label.
Test empty input and a group whose amount values are all null. A generic coalesce can be appropriate only if replacing those states with zero matches the measure contract.
Review filters and joins before grouping
A join that multiplies source rows can inflate every grouping level consistently, making the report look internally coherent while still wrong. Confirm the expected join cardinality before aggregating.
Keep row-level filters and group-level filters distinct. A condition applied before aggregation changes the source set; a condition on an aggregate result changes which groups are displayed.
Apply tenant and authorization scope before exposing any level, including grand totals. A correct subtotal calculation can still leak information if the overall aggregate crosses an access boundary.
Order and validate the final result
Grouping operations do not supply a complete presentation order. Use explicit ordering that places detail and subtotal rows according to the product’s contract, with stable tie rules where needed.
Validate totals using a small hand-checkable dataset with two dimensions, null values, repeated entities, and an empty case. Then examine performance on representative production-scale data with the normal resource limits.
Keep the result’s grouping markers and measure definitions documented. The strongest grouping-set report is one whose consumers cannot accidentally confuse a detail group, an unknown dimension, and a grand total.
Frequently asked questions
Is every null dimension a subtotal?
No. Source data may also contain null. Use grouping information to distinguish the states.
Can I sum all returned rows for the total?
Not when the output contains overlapping detail and subtotal levels. Select the intended level instead.
Does CUBE always improve a report?
No. It produces all grouping combinations, which can add unnecessary work and ambiguous output.
See the PostgreSQL table-expression documentation for grouping sets, ROLLUP, CUBE, and structural null behavior.
For a complementary workflow, read PostgreSQL Window Frames: Define the Calculation Boundary.
