Sql Aggregates Advanced — Free Sql Tutorial

TL;DR: Learn Sql Aggregates Advanced in Sql with a free, beginner-friendly tutorial, examples and practice for Indian students on Syllab.in.

Sql Aggregates Advanced in Sql

Beyond basic GROUP BY, SQL offers ROLLUP and CUBE for multi-dimensional aggregation — generating subtotals and grand totals automatically. Essential for report generation.

ROLLUP generates hierarchical subtotals: GROUP BY class_id, subject WITH ROLLUP gives totals per class_id+subject, then per class_id only, then grand total. Column values for rolled-up levels appear as NULL.

CUBE generates all possible subtotal combinations. For GROUP BY a, b, c WITH CUBE, you get subtotals for all 8 combinations: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ().

Conditional aggregation: SUM(CASE WHEN ...) or COUNT(CASE WHEN ...) lets you aggregate different subsets in a single pass — much faster than multiple queries or subqueries.

-- ROLLUP (hierarchical subtotals):
SELECT class_id, subject, SUM(marks)
FROM student_marks
GROUP BY class_id, subject WITH ROLLUP;

-- GROUPING() identifies which rows are subtotals:
SELECT
    GROUPING(class_id) AS is_class_subtotal,
    class_id,
    SUM(marks)
FROM student_marks
GROUP BY class_id WITH ROLLUP;

Learn Sql Aggregates Advanced step by step with Syllab's free interactive Sql tutorial — runnable code examples, practice exercises and instant AI feedback, all free with no signup. Explore the full Sql course →