GROUP BY ROLLUP Subtotals with ORDER BY
GROUP BY ROLLUP Subtotals with ORDER BY is a SQL statement in the Aggregate Functions category. ORDER BY on ROLLUP results places subtotal rows according to sort order. NULLs represent subtotal aggregations. The syntax is SELECT col1, col2, SUM(val) FROM table GROUP BY ROLLUP(col1, col2) ORDER BY col1, col2;. It returns ordered rollup with grouping. A typical example: SELECT CASE WHEN GROUPING(department) = 1 THEN 'ALL DEPARTMENTS' ELSE department END AS dept, CASE WHEN GROUPING(job_title) = 1 THEN 'ALL ROLES' ELSE job_title END AS role, SUM(salary) AS total_salary, GROUPING(department) AS dept_grp, GROUPING(job_title) AS role_grp FROM employees GROUP BY ROLLUP(department, job_title) ORDER BY GROUPING(department), department, GROUPING(job_title), job_title; -- GROUPING() function identifies subtotal rows -- ORDER BY with GROUPING() controls subtotal row placement -- GROUPING() returns: -- 0 = detail… A close relative is COUNT(), which returns the number of rows (or non-NULL values) in a group. A close relative is SUM(), which returns the sum of all non-NULL values in a group. Works with numeric columns.