SQL Aggregate and Window Functions Table
| Function | Type | What it returns |
|---|---|---|
| COUNT(*) | Aggregate | Total number of rows (including NULLs) |
| COUNT(col) | Aggregate | Number of rows where col is not NULL |
| SUM(col) | Aggregate | Sum of all non-NULL values |
| AVG(col) | Aggregate | Arithmetic mean of non-NULL values |
| MIN(col) | Aggregate | Smallest value |
| MAX(col) | Aggregate | Largest value |
| ROW_NUMBER() | Window | Sequential number per row within a partition |
| RANK() | Window | Rank with gaps for ties (1, 2, 2, 4) |
| DENSE_RANK() | Window | Rank without gaps for ties (1, 2, 2, 3) |
| LAG(col) | Window | Value from the previous row |
| LEAD(col) | Window | Value from the next row |
SQL functions fall into two families that behave fundamentally differently: aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse multiple rows into one result per group, while window functions (ROW_NUMBER, RANK, LAG, LEAD) compute across rows but keep every row in the output.
The COUNT(*) vs COUNT(col) distinction is the most common silent bug in SQL: COUNT(col) skips NULL values, so COUNT(email) on a table with nullable emails returns a smaller number than COUNT(*).
How to use
- Filter by function name, type (aggregate/window), or what it returns.
- Click any function to copy it into a SQL query.
- Read the type column: aggregate functions collapse rows with GROUP BY, window functions preserve rows with OVER.
Frequently asked questions
What is the difference between RANK() and DENSE_RANK()?
RANK() leaves gaps after ties: scores 95, 95, 90 get ranks 1, 1, 3. DENSE_RANK() has no gaps: 1, 1, 2. Use RANK() for competition-style rankings (two gold medals means no silver), DENSE_RANK() for pagination-style ordering where you need consecutive groups.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before aggregation; HAVING filters after aggregation. WHERE cannot use aggregate functions because rows haven’t been grouped yet. The pattern: WHERE first (remove rows), then GROUP BY, then HAVING (filter groups), then SELECT, then ORDER BY.
What does LAG() and LEAD() do?
LAG(col) retrieves the value from the previous row and LEAD(col) from the next row - both within a partition defined by OVER(PARTITION BY ... ORDER BY ...). The classic use case: month-over-month growth (current month revenue minus LAG(revenue)).
When should I use COUNT(col) instead of COUNT(*)?
Almost never - COUNT(*) is what you want 99% of the time. COUNT(col) counts only non-NULL values, which is useful for data quality checks: if COUNT(email) < COUNT(*) then some rows have missing emails. The discrepancy between the two counts IS the data quality metric.