GROUPING SETS

GROUPING SETS is a SQL statement in the Aggregate Functions category. Explicitly defines which grouping combinations to compute. More efficient than CUBE when only specific groups needed. The syntax is SELECT col1, col2, SUM(value) FROM table GROUP BY GROUPING SETS((col1), (col2), ());. It returns selected grouping levels. A typical example: SELECT department, job_title, SUM(salary) AS total FROM employees GROUP BY GROUPING SETS( (department), -- subtotal by department (job_title), -- subtotal by job title () -- grand total ); -- Only 3 grouping levels (vs 4 from CUBE with 2 columns) -- More efficient: skips unwanted combinations 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. A close relative is AVG(), which returns the average (mean) of all non-NULL values in a group.