PostgreSQL Advanced Aggregations: GROUPING SETS, ROLLUP, CUBE, and Multi-Dimensional KPI Analysis

Go beyond GROUP BY with PostgreSQL advanced aggregations. 

In this article, we use GROUPING SETS, ROLLUP, CUBE, conditional aggregation and FILTER to turn the Smart Factory database into multi-dimensional management reports.

This time, the examples are not illustrative. The result tables below were calculated directly from the supplied Smart Factory PostgreSQL dump. The dataset contains 1,143 production orders, 17,167 production events, 82 downtime events, 8 maintenance events, 1,143 quality inspections and 2,172 energy measurements.

The objective: learn the SQL technique, see the real result, and interpret what the result means for a factory.

1. Why simple GROUP BY is not enough

A production manager may want production by line, by product, by line and product, and for the entire factory. Writing separate queries for every level quickly becomes repetitive.

SELECT
    pl.line_code,
    pl.line_name,
    SUM(po.actual_quantity) AS actual_quantity
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY actual_quantity DESC;

2. GROUPING SETS: several aggregation levels in one query

GROUPING SETS lets us explicitly choose the aggregation levels we want.

SELECT
    pl.line_code,
    p.product_code,
    SUM(po.actual_quantity) AS actual_quantity
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
JOIN smart_factory.products p
    ON p.product_id = po.product_id
GROUP BY GROUPING SETS (
    (pl.line_code, p.product_code),
    (pl.line_code),
    ()
);

Before looking at the complete result, start with the real line-level totals:

Line	Orders	Planned	Actual	Rejected	Fulfillment	Reject rate
L01	381	129,495	126,277	1,365	97.51%	1.08%
L02	381	127,560	121,759	2,807	95.45%	2.31%
L03	381	128,683	124,973	1,295	97.12%	1.04%

Interpretation: L02 is clearly the weak line: 95.45% fulfillment versus 97.51% for L01, while its reject rate is 2.31%—more than twice L01's 1.08%.

3. The real line + product result

Now look at the 15 line/product combinations generated by the same aggregation logic:

Line	Product	Orders	Planned	Actual	Rejected	Fulfillment	Reject rate
L01	P1001	79	25,960	25,341	285	97.62%	1.12%
L01	P1002	84	30,051	29,328	324	97.59%	1.10%
L01	P2001	59	19,440	18,984	213	97.65%	1.12%
L01	P2002	83	28,620	27,910	283	97.52%	1.01%
L01	P3001	76	25,424	24,714	260	97.21%	1.05%
L02	P1001	75	26,626	25,458	557	95.61%	2.19%
L02	P1002	73	24,430	23,282	531	95.30%	2.28%
L02	P2001	72	23,352	22,166	558	94.92%	2.52%
L02	P2002	87	27,894	26,532	662	95.12%	2.50%
L02	P3001	74	25,258	24,321	499	96.29%	2.05%
L03	P1001	73	23,489	22,794	236	97.04%	1.04%
L03	P1002	86	29,021	28,225	306	97.26%	1.08%
L03	P2001	73	24,614	23,822	259	96.78%	1.09%
L03	P2002	61	21,679	21,067	184	97.18%	0.87%
L03	P3001	88	29,880	29,065	310	97.27%	1.07%

The pattern is operationally interesting. The weakest combination is L02 + P2001 with 94.92% fulfillment and a 2.52% reject rate. L02 + P2002 is similarly problematic at 95.12% fulfillment and 2.50% rejects.

4. Product-level subtotals

The same concept can produce a product subtotal:

GROUP BY GROUPING SETS (
    (pl.line_code, p.product_code),
    (pl.line_code),
    (p.product_code),
    ()
);

Product	Orders	Planned	Actual	Rejected	Fulfillment	Reject rate
P1001	227	76,075	73,593	1,078	96.74%	1.46%
P1002	243	83,502	80,835	1,161	96.81%	1.44%
P2001	204	67,406	64,972	1,030	96.39%	1.59%
P2002	231	78,193	75,509	1,129	96.57%	1.50%
P3001	238	80,562	78,100	1,069	96.94%	1.37%

P3001 has the best fulfillment rate at 96.94%, while P2001 has the lowest at 96.39% and the highest product-level reject rate at 1.59%.

5. The factory grand total

The empty grouping set () represents the complete factory total.

Scope	Orders	Planned	Actual	Rejected	Fulfillment	Reject rate
FACTORY TOTAL	1,143	385,738	373,009	5,467	96.70%	1.47%

Across the complete dataset, the factory planned 385,738 units and produced 373,009, giving a fulfillment rate of 96.70%. Total rejected quantity is 5,467 units.

6. GROUPING(): distinguish subtotal rows from data

SELECT
    pl.line_code,
    p.product_code,
    SUM(po.actual_quantity) AS actual_quantity,
    GROUPING(pl.line_code) AS line_grouped,
    GROUPING(p.product_code) AS product_grouped
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
JOIN smart_factory.products p
    ON p.product_id = po.product_id
GROUP BY GROUPING SETS (
    (pl.line_code, p.product_code),
    (pl.line_code),
    ()
);

This is important when a result is consumed by an application: a NULL product in a subtotal row should not be confused with a genuinely missing product value.

7. ROLLUP: hierarchical reporting

When the hierarchy is natural, ROLLUP is shorter:

SELECT
    pl.line_code,
    p.product_code,
    SUM(po.actual_quantity) AS actual_quantity
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
JOIN smart_factory.products p
    ON p.product_id = po.product_id
GROUP BY ROLLUP (
    pl.line_code,
    p.product_code
);

Conceptually:

Line + Product
      ↓
Line subtotal
      ↓
Factory total

For this dataset, the line subtotals above are the key ROLLUP result to inspect. They reveal that all three lines processed exactly 381 orders, but L02 converted those orders into materially lower output and higher rejects.

8. CUBE: multidimensional analysis

SELECT
    pl.line_code,
    p.product_code,
    SUM(po.actual_quantity) AS actual_quantity
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
JOIN smart_factory.products p
    ON p.product_id = po.product_id
GROUP BY CUBE (
    pl.line_code,
    p.product_code
);

CUBE produces the line + product combinations, line subtotals, product subtotals and factory total. The real subtotals are shown above, so the important lesson is the structure: one query can support several analytical perspectives.

9. Conditional aggregation with FILTER()

The production orders also have operational statuses. The real dataset contains:

Status	Orders
COMPLETED	1,106
CANCELLED	19
IN_PROGRESS	18

That means 1,106 of 1,143 orders are COMPLETED, while 19 are CANCELLED and 18 are IN_PROGRESS.

SELECT
    pl.line_code,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (
        WHERE po.status = 'COMPLETED'
    ) AS completed_orders,
    COUNT(*) FILTER (
        WHERE po.status <> 'COMPLETED'
    ) AS non_completed_orders
FROM smart_factory.production_orders po
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
GROUP BY pl.line_code;

FILTER is especially convenient when several conditional KPIs must appear beside one another.

10. Quality: the same aggregation idea

The quality inspections are linked to production orders, products and production lines. Calculate defect rate at line/product level:

SELECT
    pl.line_code,
    p.product_code,
    SUM(qi.inspected_quantity) AS inspected_quantity,
    SUM(qi.passed_quantity) AS passed_quantity,
    SUM(qi.failed_quantity) AS failed_quantity,
    ROUND(
        100.0 * SUM(qi.failed_quantity)
        / NULLIF(SUM(qi.inspected_quantity), 0),
        2
    ) AS defect_rate_pct
FROM smart_factory.quality_inspections qi
JOIN smart_factory.production_orders po
    ON po.order_id = qi.order_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = po.line_id
JOIN smart_factory.products p
    ON p.product_id = po.product_id
GROUP BY pl.line_code, p.product_code;

Here are the real results:

Line	Product	Inspected	Passed	Failed	Defect rate
L01	P1001	6,320	6,211	109	1.72%
L01	P1002	6,720	6,599	121	1.80%
L01	P2001	4,720	4,634	86	1.82%
L01	P2002	6,640	6,519	121	1.82%
L01	P3001	6,080	5,976	104	1.71%
L02	P1001	6,000	5,775	225	3.75%
L02	P1002	5,840	5,580	260	4.45%
L02	P2001	5,760	5,537	223	3.87%
L02	P2002	6,960	6,705	255	3.66%
L02	P3001	5,920	5,685	235	3.97%
L03	P1001	5,840	5,736	104	1.78%
L03	P1002	6,880	6,744	136	1.98%
L03	P2001	5,840	5,729	111	1.90%
L03	P2002	4,880	4,779	101	2.07%
L03	P3001	7,040	6,894	146	2.07%

The quality signal reinforces the production signal: L02 has materially higher defect rates. Its worst combination is L02 + P1002 at 4.45%.

11. Energy: machine and line aggregation

The energy table contains daily machine measurements. We can aggregate it with ROLLUP or ordinary grouping.

SELECT
    pl.line_code,
    m.machine_code,
    SUM(ec.energy_kwh) AS total_energy_kwh,
    AVG(ec.power_kw) AS avg_power_kw
FROM smart_factory.energy_consumption ec
JOIN smart_factory.machines m
    ON m.machine_id = ec.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
GROUP BY
    pl.line_code,
    m.machine_code;

Line	Machine	Energy kWh	Avg power kW
L01	M001	17,288.99	10.90
L01	M002	20,192.54	12.89
L01	M003	11,198.28	7.04
L02	M004	41,762.06	26.27
L02	M005	48,470.40	30.81
L02	M006	39,496.72	24.70
L02	M007	32,657.33	20.63
L03	M008	18,695.97	11.89
L03	M009	14,031.87	8.80
L03	M010	9,265.35	5.83
L03	M011	26,083.35	16.64
L03	M012	14,654.03	9.35

12. Energy per produced unit

A raw energy total is not enough. A more useful manufacturing KPI is energy intensity:

energy_kwh / NULLIF(actual_quantity, 0)
    AS kwh_per_unit

Line	Energy kWh	Actual units	kWh / unit
L01	48,679.81	126,277	0.3855
L02	162,386.51	121,759	1.3337
L03	82,730.58	124,973	0.6620

The difference is striking: L02 consumes about 1.3337 kWh per produced unit, compared with 0.3855 for L01 and 0.6620 for L03. This is an important example of why industrial analysis should normalize resource consumption by output.

13. What the real data tells us

There is a coherent industrial signal in the dataset.

L02 is the weakest production line: 95.45% fulfillment and 2.31% reject rate. Its quality results are also worse, and its energy intensity is much higher at 1.3337 kWh/unit. This is exactly the type of multi-dimensional pattern that advanced SQL is designed to expose.

The dataset documentation deliberately describes a discoverable industrial story in which a machine on Line 2 progressively deteriorates and affects downtime, production, quality and energy before maintenance intervention. The aggregated results above therefore give us a starting point for the next investigation: which machine on L02 is responsible?

14. GROUPING SETS vs ROLLUP vs CUBE

Technique	Best use	Question answered
GROUPING SETS	Explicit aggregation levels	Which exact summaries do I need?
ROLLUP	Hierarchical totals	How do detail → subtotal → total relate?
CUBE	Multidimensional analysis	What happens across every dimension combination?
FILTER()	Conditional KPIs	How many rows meet each condition?

15. Combining the techniques

The advanced SQL workflow is now becoming clear:

Raw industrial data
        ↓
      JOIN
        ↓
       CTE
        ↓
GROUPING SETS / ROLLUP / CUBE
        ↓
Window functions
        ↓
Industrial KPI
        ↓
Dashboard / investigation

The database already includes analytical views such as v_machine_monthly_performance, showing that this model naturally leads toward reusable analytical layers.

16. Your Smart Factory SQL challenge

1. Reproduce the line/product production table.
2. Add line subtotals and the factory total using GROUPING SETS.
3. Rebuild the same hierarchy using ROLLUP.
4. Use CUBE to generate line and product perspectives.
5. Add GROUPING() flags.
6. Calculate completed and non-completed orders with FILTER().
7. Calculate quality defect rate by line/product.
8. Calculate energy consumption by machine.
9. Calculate kWh per produced unit by line.
10. Finally, investigate L02 and identify the machine-level cause.

17. What you learned

• GROUPING SETS for custom multi-level aggregation.
• ROLLUP for hierarchical subtotals.
• CUBE for multidimensional aggregation.
• GROUPING() for identifying subtotal and total rows.
• FILTER() for conditional aggregates.
• How to interpret real production, quality and energy results.
• How advanced aggregation can reveal an industrial signal that deserves deeper investigation.

Practice with the complete Smart Factory dataset

The complete PostgreSQL dataset contains the interconnected manufacturing environment used throughout this SQL series: production orders, machines, production events, downtime, maintenance, quality and energy data.

Smart Factory Manufacturing Dataset v0.1 — $9

→ GET THE COMPLETE DATASET ON GUMROAD

Smart Factory SQL Series — Article 12
PostgreSQL • Advanced Aggregations • GROUPING SETS • ROLLUP • CUBE • Industrial Analytics • Industry 4.0