Groups, conditions and dates

This lesson covers GROUP BY + JOIN, HAVING, ORDER BY aggregate, EXTRACT, COUNT FILTER / CASE. Read it, then solve questions 31–40 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

Group after joining

Join first, then group by a column from any joined table. The query runs in this order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

SELECT c.city, COUNT(*) AS bills
FROM sales s
JOIN customers c ON c.customer_id = s.customer_id
GROUP BY c.city;

WHERE vs HAVING

WHERE filters rows before grouping and can’t use aggregates. HAVING filters groups after grouping and can: HAVING COUNT(*) >= 3.

SELECT customer_id, SUM(quantity_sold) AS units
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(quantity_sold) > 10;

Sorting by an aggregate

You can ORDER BY an aggregate or its alias, then LIMIT to get “the top one”. Remember a tie: LIMIT 1 silently picks one of the tied rows.

SELECT customer_id, COUNT(*) AS bills
FROM sales
GROUP BY customer_id
ORDER BY bills DESC, customer_id
LIMIT 3;

Taking dates apart

EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d), EXTRACT(DAY FROM d) return numbers. DATE_TRUNC('month', d) rounds down to the first of the month; TO_CHAR(d, 'YYYY-MM') gives a sortable label. When grouping by month, keep the year too, or different years merge.

SELECT EXTRACT(YEAR FROM joined_on) AS y,
       EXTRACT(MONTH FROM joined_on) AS m,
       COUNT(*) AS new_customers
FROM customers
GROUP BY y, m
ORDER BY y, m;

Counting only some rows

To count several conditions side by side in one query:

• COUNT(*) FILTER (WHERE condition) (PostgreSQL), or
• SUM(CASE WHEN condition THEN 1 ELSE 0 END) (works everywhere).

SELECT customer_id,
       COUNT(*) FILTER (WHERE quantity_sold = 1) AS single_items,
       SUM(CASE WHEN quantity_sold > 1 THEN 1 ELSE 0 END) AS multi_items
FROM sales
GROUP BY customer_id
ORDER BY customer_id;