T-SQL clause selection: WHERE vs GROUP BY vs ORDER BY vs window function
Verdict: WHERE restricts which rows return. GROUP BY with an aggregate collapses detail rows into group totals. ORDER BY only sorts. A window function with PARTITION BY and ORDER BY computes running totals without collapsing the detail grain.
| Criterion | WHERE | GROUP BY | ORDER BY | Window function |
|---|---|---|---|---|
| Purpose | Restrict which rows return | Aggregate rows into summary groups | Sort the returned rows | Compute per-row values across a partition |
| Row grain | Preserves rows meeting the condition | Collapses to one row per group | Preserves rows, reorders | Preserves detail grain |
| Example | WHERE OrderDate in current fiscal year | GROUP BY Category, Month with SUM | ORDER BY OrderDate | SUM(...) OVER (PARTITION BY CustomerId ORDER BY OrderDate) |
| Choose when | Current fiscal year only | Total sales by category and month | Present rows in date order | Running year-to-date total per customer |
Rules
- Use a WHERE clause to restrict returned rows by a condition, such as limiting to the current fiscal year.
- Use GROUP BY with an aggregate like SUM to summarise detail rows to a chosen grain, such as category and month.
- Use a window function, SUM(...) OVER (PARTITION BY ... ORDER BY ...), for a running total that preserves detail rows.
Traps
- GROUP BY aggregates and ORDER BY sorts; neither restricts which rows return.
- A plain GROUP BY collapses to one row per group and cannot produce a row-by-row running total.
- DISTINCT removes duplicate rows; it does not aggregate or filter.