12. Grouping Data with GROUP BY

Core Idea: GROUP BY aggregates rows sharing identical values in specified columns into concise summary rows for computation alongside aggregate functions.

Syntax Shape

SELECT Category, COUNT(*) FROM Products GROUP BY Category;

Multiple Grouping Columns

            SELECT Category, SubCategory, COUNT(*), AVG(Price) 
            FROM Products 
            GROUP BY Category, SubCategory;
        

Practical Examples

Analyzing product distribution across categories, computing regional sales totals, and department payroll sums.

Advanced Deep Dive ADVANCED

Grouping Sets: GROUP BY GROUPING SETS allows multiple independent group aggregations in a single unified query.

ROLLUP & CUBE: GROUP BY ROLLUP produces hierarchical subtotals with grand totals, whereas CUBE generates all cross-dimensional combinations.

HAVING Clause: Use HAVING instead of WHERE to filter groups post-aggregation.

NULL Handling: Rows with NULL values in grouping columns are consolidated into their own dedicated group.

Performance & TempDB: Large grouping operations benefit from indexes on group columns; otherwise, hash or stream aggregate operators may spill intermediate sort tables to tempdb.