10. Grouping Data (GROUP BY)

Core Idea: The GROUP BY clause consolidates multiple rows sharing common attributes into single summary rows (such as aggregating order volume per customer or total sales per calendar year).

Syntax Structure

SELECT CustomerID, SUM(Amount) FROM Sales GROUP BY CustomerID;

Why Use It?

Enables aggregate calculations across categories, such as totals (SUM), arithmetic means (AVG), counts (COUNT), and extrema (MIN/MAX).

Practical Scenarios

Computing quarterly regional revenue, customer lifetime value, and order frequency by distribution center.

Advanced Deep Dive ADVANCED

Stream vs. Hash Aggregate: Pre-sorted input permits the execution engine to perform stream aggregation (minimal memory overhead); unsorted or high-cardinality data triggers hash aggregation.

Functional Dependency: In standard SQL, non-aggregated columns must appear in the GROUP BY list unless they are functionally dependent on primary key columns.

Multi-Level Aggregates: Using GROUPING SETS, ROLLUP, or CUBE generates hierarchical and matrix subtotals within a single query pass.

Distinct Aggregation Cost: Operations like COUNT(DISTINCT Col) necessitate dedicated sorting or hash-table tracking to eliminate duplicates before counting.

Cardinality & Statistics: Up-to-date column distribution statistics determine optimal memory grants and prevent query spills to tempdb during aggregation.