Using SQL Aggregate Functions
1. Counting Rows
| Form | Counts |
|---|---|
COUNT(*) | All rows including NULLs |
COUNT(col) | Rows where col IS NOT NULL |
COUNT(DISTINCT col) | Unique non-null values |
COUNT(*) FILTER (WHERE ...) | Conditional count (PG, standard) |
2. Calculating Sums
| Function | Notes |
|---|---|
SUM(col) | Skips NULLs; returns NULL on empty set |
SUM(col) FILTER (WHERE ...) | Conditional sum |
SUM(CASE WHEN ... THEN x ELSE 0 END) | Pre-SQL:2003 conditional sum |
3. Finding Averages
| Function | Type |
|---|---|
| AVG(col) | Arithmetic mean (NULLs skipped) |
| PERCENTILE_CONT(0.5) | Median (continuous) |
| PERCENTILE_DISC(0.95) | P95 (discrete) |
| STDDEV, VARIANCE | Spread |
| MODE() | Most frequent value (PG) |
4. Getting Minimum Values
| Use | Example |
|---|---|
| MIN(col) | SELECT MIN(price) FROM products; |
| Earliest date | MIN(created_at) |
| Per group | MIN(col) ... GROUP BY |
5. Getting Maximum Values
| Use | Example |
|---|---|
| MAX(col) | SELECT MAX(score) FROM games; |
| Latest version | MAX(version) ... GROUP BY |
| Greatest-N-per-group | Use window functions, not MAX alone |
6. Grouping Results
| Clause | Effect |
|---|---|
| GROUP BY col | One row per distinct value |
| GROUP BY a, b | Composite grouping |
| GROUP BY ROLLUP(a,b) | Subtotals + grand total |
| GROUP BY CUBE(a,b) | All combinations |
| GROUP BY GROUPING SETS | Custom group combos |
Example: Sales by Region and Month
SELECT region, date_trunc('month', placed_at) AS month,
SUM(total) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY region, date_trunc('month', placed_at)
ORDER BY month, region;7. Filtering Groups
| Clause | Filters |
|---|---|
| WHERE | Rows before grouping |
| HAVING | Groups after aggregation |
| QUALIFY (Snowflake, DuckDB) | Window function output |
Example: HAVING
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10;8. Using Multiple Aggregations
| Pattern | Example |
|---|---|
| Multiple aggs | SUM, AVG, MIN, MAX in same SELECT |
| Filtered aggs | COUNT(*) FILTER (WHERE status='paid') |
| Pivot | Multiple CASE/FILTER aggs to columnize |
9. Handling NULL in Aggregations
| Function | NULL Treatment |
|---|---|
| SUM/AVG/MIN/MAX | Skip NULL inputs |
| COUNT(col) | Skip NULL inputs |
| COUNT(*) | Count all rows |
| Empty set | SUM/AVG return NULL; COUNT returns 0 |
| COALESCE(SUM(col), 0) | Replace NULL result with 0 |
10. Creating Rollups and Cubes
| Feature | Generates |
|---|---|
| ROLLUP(a,b,c) | (a,b,c), (a,b), (a), () |
| CUBE(a,b) | (a,b), (a), (b), () |
| GROUPING SETS | Explicit list of group combos |
| GROUPING(col) | 1 if col is rolled up, else 0 |