Aggregate functions summarize sets of rows. COUNT(*) tallies every row including NULLs, COUNT(column) tallies non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values. SUM totals a numeric column, AVG computes its mean, and MIN and MAX return the smallest and largest values. When used without GROUP BY, aggregates apply to the entire result set; with GROUP BY, they produce one summary row per group. Aggregates also appear combined with set functions like GROUP_CONCAT (MySQL), STRING_AGG (PostgreSQL), and LISTAGG (Oracle), which concatenate string values across rows into a single delimited string.
Window functions perform calculations across rows related to the current row without collapsing them into summary groups. They are written with an OVER() clause that defines the partition—the set of rows the function considers—and optionally an ordering and a frame clause. ROW_NUMBER assigns a unique sequential integer to each row in the partition, RANK assigns the same rank to ties while skipping subsequent numbers, and DENSE_RANK assigns the same rank to ties without skipping. NTILE(n) buckets rows into n approximately equal groups, useful for percentile-style reporting.
Other powerful window functions navigate the partition directly. LAG returns a value from a previous row and LEAD from a following row, making it easy to compute differences from one period to the next, while FIRST_VALUE and LAST_VALUE fetch the boundary values of the window. The frame clause, written as ROWS BETWEEN or RANGE BETWEEN, controls exactly which rows within the partition contribute to the calculation—ROWS uses physical positions while RANGE uses logical value ranges. The distinction between GROUP BY and PARTITION BY is fundamental: GROUP BY produces one row per group, while PARTITION BY performs the calculation within rows that remain individually visible.