11. Aggregate Functions: COUNT, SUM, AVG

Core Idea: Aggregate functions summarize dataset rows into singular scalar values. COUNT tallies occurrences, SUM totals numeric columns, and AVG computes arithmetic means.

Syntax Structure

SELECT COUNT(*) FROM Customers;

SELECT SUM(Price) FROM Products;

SELECT AVG(Price) FROM Products;

Combining Multiple Aggregates

SELECT COUNT(*), SUM(Price), AVG(Price) FROM Products;

Practical Scenarios

Calculating product catalog size, inventory balance valuations, transaction volume tallies, and mean order amounts.

Advanced Deep Dive ADVANCED

NULL Handling: Standard aggregate functions (SUM, AVG, COUNT(column)) ignore NULL entries. Only COUNT(*) accounts for all records including rows containing NULLs.

Grouping with GROUP BY: Combine aggregates with GROUP BY to produce subtotal metrics across categorical dimensions.

Filtering Groups with HAVING: Post-aggregation condition filters must be specified in the HAVING clause rather than WHERE.

Window Function Variants: SUM() OVER(...) and AVG() OVER(...) compute running cumulative totals and moving averages without collapsing row granularity.

Index Optimization: Aggregation queries benefit significantly from covering indexes on the summarized and grouped columns to avoid full table scans.