Aggregates and GROUP BY

Aggregate functions collapse rows: COUNT, SUM, AVG, MIN, MAX. FILTER clause adds conditional aggregates without extra subqueries.

GROUP BY basics

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100;

Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).

FILTER clause

SELECT
  COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;

GROUPING SETS preview

SELECT customer_id, status, COUNT(*)
FROM orders
GROUP BY GROUPING SETS ((customer_id, status), (customer_id), ());

Rollup/cube reports use GROUPING SETS—handy for analytics dashboards.

Important interview questions and answers

1. Q: WHERE vs HAVING?
   A: WHERE filters rows before grouping; HAVING filters groups after aggregation.
2. Q: COUNT(*) vs COUNT(col)?
   A: COUNT(*) counts rows; COUNT(col) ignores NULLs in col.

Self-check

1. Where do you filter on SUM(total)?
2. What does FILTER add to aggregates?

Pitfall: Non-aggregated columns must appear in GROUP BY in strict SQL.