Aggregate Functions
Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize rows. Most aggregates ignore NULL inputs; COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column. GROUP BY creates groups, and HAVING filters groups after aggregation.
Be careful when joining before aggregating: a one-to-many join can duplicate rows and inflate sums. Aggregate each relationship at the correct level or use distinct/structured subqueries when appropriate. COALESCE can provide a fallback for a NULL aggregate result, but choose the fallback based on business meaning.
Revenue by customer
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total) AS revenue,
AVG(total) AS average_order
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000
ORDER BY revenue DESC;💡 The query calculates metrics per customer and keeps only groups whose total revenue exceeds 10000. HAVING filters grouped results, while WHERE filters individual rows before grouping.
Key Takeaways
- COUNT(*) counts rows; COUNT(column) excludes NULL values.
- WHERE filters rows before grouping; HAVING filters groups.
- Check join cardinality before calculating sums.
Knowledge Check
Which clause filters groups after aggregation?