Lesson 3: Summarize rows by group
Aggregates reduce many input rows to one output per group. In PostgreSQL, every selected expression must be grouped or aggregated.
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS lifetime_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) >= 500
ORDER BY lifetime_total DESC;Aggregate reference
12 examples shown
| Expression | Purpose | Example |
|---|---|---|
| COUNT(*) | Counts rows, including rows with null columns. | COUNT(*) |
| COUNT(column) | Counts non-NULL values in a column. | COUNT(shipped_at) |
| COUNT(DISTINCT) | Counts distinct non-null values. | COUNT(DISTINCT customer_id) |
| SUM | Adds non-null numeric values. | SUM(total) |
| AVG | Mean of non-null numeric values. | AVG(total) |
| MIN / MAX | Smallest or largest comparable value. | MIN(order_date), MAX(order_date) |
| STRING_AGG | Concatenates values with a delimiter (PostgreSQL). | STRING_AGG(product_name, ', ' ORDER BY product_name) |
| BOOL_AND / BOOL_OR | All or any Boolean inputs are true (PostgreSQL). | BOOL_AND(is_valid) |
| GROUP BY | Defines one result row per distinct grouping key. | GROUP BY region, date_trunc('month', order_date) |
| HAVING | Filters groups after aggregation. | HAVING COUNT(*) > 5 |
| FILTER | Applies a condition to one aggregate only (PostgreSQL). | COUNT(*) FILTER (WHERE status='paid') |
| COALESCE | Replaces NULL with the first non-null fallback. | COALESCE(SUM(total),0) |
WHERE versus HAVING
Use WHERE to filter source rows before groups are calculated. Use HAVING to filter results of aggregate expressions. Be cautious when grouping by timestamps: grouping by the exact timestamp may create one group per row; truncate to a date or reporting period where appropriate.
Progress stays in this browser.