Aggregate functions compute a single result from a set of rows. Window functions (OVER clause) compute aggregates over a partition of rows without collapsing them — one of the most powerful SQL features.
| Statement / Feature | Syntax | Description |
|---|---|---|
| COUNT(*) | SELECT COUNT(*) FROM table | Count all rows including nulls |
| COUNT(col) | SELECT COUNT(col) FROM table | Count non-null values in column |
| COUNT(DISTINCT) | SELECT COUNT(DISTINCT col) FROM table | Count unique non-null values |
| SUM | SELECT SUM(salary) FROM employees | Sum of all values |
| AVG | SELECT AVG(score) FROM results | Arithmetic mean (ignores nulls) |
| MIN | SELECT MIN(hire_date) FROM employees | Minimum value |
| MAX | SELECT MAX(salary) FROM employees | Maximum value |
| GROUP_CONCAT | GROUP_CONCAT(name SEPARATOR ', ') | Concatenate strings per group (MySQL) |
| STRING_AGG | STRING_AGG(name, ', ' ORDER BY name) | Concatenate strings per group (PostgreSQL) |
| ARRAY_AGG | ARRAY_AGG(col ORDER BY col) | Aggregate into array (PostgreSQL) |
| JSON_AGG | JSON_AGG(row_to_json(t)) | Aggregate rows into JSON array (PostgreSQL) |
| STDDEV | STDDEV(col) / STDDEV_POP(col) | Standard deviation |
| VARIANCE | VARIANCE(col) | Statistical variance |
| PERCENTILE | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) | Median / percentile |
| OVER() | SUM(salary) OVER () | Window function — aggregate without grouping |
| PARTITION BY | SUM(salary) OVER (PARTITION BY dept_id) | Window partition |
| ORDER BY in OVER | ROW_NUMBER() OVER (ORDER BY salary DESC) | Ordered window |
| ROW_NUMBER() | ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) | Sequential row number per partition |
| RANK() | RANK() OVER (ORDER BY score DESC) | Rank with gaps for ties |
| DENSE_RANK() | DENSE_RANK() OVER (ORDER BY score DESC) | Rank without gaps for ties |
| NTILE() | NTILE(4) OVER (ORDER BY salary) | Divide into N roughly equal buckets |
| LAG() | LAG(salary, 1) OVER (ORDER BY hire_date) | Previous row value |
| LEAD() | LEAD(salary, 1) OVER (ORDER BY hire_date) | Next row value |
| FIRST_VALUE() | FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) | First value in window |
| LAST_VALUE() | LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC ROWS UNBOUNDED PRECEDING) | Last value in window |
| RUNNING TOTAL | SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) | Running/cumulative total |
| MOVING AVG | AVG(amount) OVER (ORDER BY date ROWS 6 PRECEDING) | 7-day moving average |
-- Aggregate Functions & Window Functions
-- Sample data setup
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
rep_name VARCHAR(100),
region VARCHAR(50),
sale_date DATE,
amount DECIMAL(10,2),
product VARCHAR(100)
);
INSERT INTO sales (rep_name,region,sale_date,amount,product) VALUES
('Alice','East','2026-01-15',4500,'Python Course'),
('Alice','East','2026-02-10',6200,'Go Course'),
('Bob', 'West','2026-01-20',3800,'Java Course'),
('Bob', 'West','2026-03-05',5100,'Rust Course'),
('Carol','East','2026-01-08',7200,'Data Science'),
('Carol','East','2026-02-22',4900,'ML Course'),
('Dave', 'West','2026-01-30',2900,'Ruby Course'),
('Dave', 'West','2026-03-12',6800,'Cloud Course');
-- ── Basic aggregates ─────────────────────────────────────────
SELECT
rep_name,
COUNT(*) AS deals,
SUM(amount) AS total_sales,
AVG(amount) AS avg_deal,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM sales
GROUP BY rep_name
ORDER BY total_sales DESC;
-- ── Region summary with HAVING ────────────────────────────────
SELECT region, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region
HAVING SUM(amount) > 10000;
-- ── Window: running total ─────────────────────────────────────
SELECT sale_date, rep_name, amount,
SUM(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales ORDER BY sale_date;
-- ── Window: rank within region ───────────────────────────────
SELECT rep_name, region, SUM(amount) AS total,
RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rank_in_region,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS overall_rank
FROM sales
GROUP BY rep_name, region
ORDER BY region, rank_in_region;
-- ── Window: vs previous period ───────────────────────────────
SELECT sale_date, amount,
LAG(amount) OVER (PARTITION BY rep_name ORDER BY sale_date) AS prev_deal,
amount - LAG(amount) OVER (PARTITION BY rep_name ORDER BY sale_date) AS change
FROM sales ORDER BY rep_name, sale_date;
-- ── NTILE quartiles ──────────────────────────────────────────
SELECT rep_name, amount,
NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales ORDER BY amount;
-- ── STRING_AGG — products per rep (PostgreSQL) ───────────────
SELECT rep_name,
STRING_AGG(product, ', ' ORDER BY sale_date) AS products_sold
FROM sales GROUP BY rep_name;