ROLLUP, CUBE, and Grouping Sets

Trilogy normally infers grouping from the non-aggregate concepts in a SELECT. Multi-level grouping is explicit and applies to the entire select:

select
    <dimensions>,
    <aggregates>,
by rollup|cube|grouping sets (...);

The grouping clause appears after the select list and before HAVING, ORDER BY, and LIMIT. Every aggregate without its own explicit by grain is computed at the same set of levels.

ROLLUP

by rollup (department, course) produces these grouping sets:

1. (department, course) — detail rows
2. (department) — department subtotals
3. () — the grand total

import enrollments as enroll;

select
    enroll.department,
    enroll.course,
    sum(enroll.credits) as total_credits,
by rollup (enroll.department, enroll.course)
order by enroll.department asc nulls first,
    enroll.course asc nulls first;

by rollup () rolls up the select's automatically inferred grain.

Labeling subtotal rows with grouping(...)

Rolled-up dimensions are represented as NULL, which is ambiguous when source data can also contain real nulls. grouping(field) returns 1 when the field has been rolled up for the current row and 0 otherwise.

select
    case
        when grouping(enroll.department) = 1 then 'ALL DEPARTMENTS'
        else enroll.department
    end as department,
    case
        when grouping(enroll.course) = 1 then 'ALL COURSES'
        else enroll.course
    end as course,
    sum(enroll.credits) as total_credits,
    --grouping(enroll.department) + grouping(enroll.course) as grouping_level,
by rollup (enroll.department, enroll.course)
having total_credits > 0
order by grouping_level asc,
    department asc nulls first,
    course asc nulls first;

The hidden --grouping_level is resolved and can be used for sorting without being returned in the final output.

CUBE

by cube (department, year) computes every subset of the dimensions:

• (department, year)
• (department)
• (year)
• ()

select
    enroll.department,
    enroll.year,
    count(enroll.id) as enrollment_count,
by cube (enroll.department, enroll.year);

Use CUBE when subtotals are meaningful independently along every supplied dimension.

GROUPING SETS

Use grouping sets to choose the exact grains to calculate. Parentheses around each set are required; () requests the grand total.

select
    enroll.department,
    enroll.course,
    enroll.year,
    sum(enroll.credits) as total_credits,
by grouping sets (
    (enroll.department, enroll.course),
    (enroll.department, enroll.year),
    (enroll.department),
    ()
);

Multiple and composite measures

The clause covers the whole select, so measures remain aligned without repeating grouping declarations:

select
    enroll.department,
    enroll.year,
    avg(enroll.credits::numeric(12, 2)) as avg_credits,
    avg(enroll.grade_points::numeric(12, 2)) as avg_grade_points,
    sum(enroll.credits) - sum(enroll.withdrawn_credits) as net_credits,
by rollup (enroll.department, enroll.year);

An aggregate with an explicit grain is independent of the select-level grouping clause. Prefer one consistent select-level grouping for reports unless that difference is intentional.

HAVING and clause order

HAVING follows the grouping clause and filters every generated level, including subtotals and the grand total:

select
    enroll.department,
    sum(enroll.credits) as total_credits,
by rollup (enroll.department)
having total_credits > 100
order by enroll.department asc nulls first;

The complete order is:

WHERE
SELECT
SUBSET|UNION JOIN
BY ROLLUP|CUBE|GROUPING SETS
HAVING
ORDER BY
LIMIT

See Query Examples for a complete labeled report and rank-over-rollup for window functions over grouped output.