SQL Aggregation Practice: GROUP BY, HAVING, COUNT, SUM, AVG

Understand this order and most aggregation bugs become obvious:

1. FROM / JOIN     -- assemble the raw rows
2. WHERE           -- filter rows (before grouping)
3. GROUP BY        -- form groups
4. aggregates      -- compute COUNT, SUM, AVG, etc.
5. HAVING          -- filter groups (after aggregation)
6. SELECT          -- pick columns and expressions
7. ORDER BY        -- sort results
8. LIMIT           -- cap output

If your numbers are wrong, ask: "Am I filtering in the right phase?"

Understanding this distinction depends on knowing how WHERE clause filtering works — WHERE filters individual rows before the GROUP BY phase.

Example: "Active products with avg price > $100"

SELECT category, AVG(price) AS avg_price
FROM products
WHERE is_active = true        -- row filter: only active products
GROUP BY category
HAVING AVG(price) > 100;      -- group filter: only high-value categories

Common mistake: Putting the aggregate condition in WHERE.

-- WRONG: This errors (can't use aggregate in WHERE)
SELECT category, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000   -- ERROR!
GROUP BY category;

-- RIGHT: Use HAVING
SELECT category, SUM(amount)
FROM orders
GROUP BY category
HAVING SUM(amount) > 1000;

This trips up everyone at least once:

-- Sample data
| id | email           |
|----|-----------------|
| 1  | alice@test.com  |
| 2  | NULL            |
| 3  | bob@test.com    |

SELECT COUNT(*)      AS total_rows,      -- 3
       COUNT(email)  AS emails_present,  -- 2 (NULL ignored)
       COUNT(id)     AS ids_present      -- 3 (no NULLs in id)
FROM users;

Rule of thumb:

• COUNT(*) = "how many rows?"
• COUNT(column) = "how many non-NULL values in this column?"

Instead of multiple queries, use CASE inside aggregates:

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue
FROM orders;

PostgreSQL/modern SQL alternative: FILTER clause

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

-- Wrong: counts all order rows
SELECT category, COUNT(*) AS buyer_count
FROM orders
GROUP BY category;

-- Right: counts unique users per category
SELECT category, COUNT(DISTINCT user_id) AS unique_buyers
FROM orders
GROUP BY category;

You can combine DISTINCT with conditions:

-- Unique users who made a purchase over $100
SELECT COUNT(DISTINCT user_id)
FROM orders
WHERE amount > 100;

"Give me all tags for each post as a comma-separated string":

-- PostgreSQL
SELECT post_id, STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;

-- MySQL
SELECT post_id, GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM post_tags
GROUP BY post_id;

-- Oracle
SELECT post_id, LISTAGG(tag, ', ') WITHIN GROUP (ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;

Tips:

• Add DISTINCT to dedupe: STRING_AGG(DISTINCT tag, ', ')
• Use ORDER BY inside the function to control sort order
• Watch out for NULL values—they're typically ignored, but check your database

Raw timestamps create one group per second—not useful.

-- PostgreSQL: DATE_TRUNC for clean buckets
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- MySQL: DATE_FORMAT or YEAR/MONTH functions
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

Common buckets:

• DATE_TRUNC('day', ts) — daily
• DATE_TRUNC('week', ts) — weekly (starts Monday in Postgres)
• DATE_TRUNC('month', ts) — monthly
• DATE_TRUNC('quarter', ts) — quarterly

JOINs multiply rows. If each order has 3 items, the order row appears 3 times after the join:

-- WRONG: If each order has multiple items, order_amount is counted multiple times
SELECT
  o.category,
  SUM(o.order_amount) AS revenue    -- INFLATED!
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.category;

Solutions:

1. Aggregate before joining:

WITH order_totals AS (
  SELECT category, SUM(order_amount) AS revenue
  FROM orders
  GROUP BY category
)
SELECT ot.category, ot.revenue, COUNT(i.id) AS items
FROM order_totals ot
LEFT JOIN orders o ON o.category = ot.category
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY ot.category, ot.revenue;

2. Use COUNT(DISTINCT ...) for counting:

SELECT
  o.category,
  COUNT(DISTINCT o.id) AS order_count  -- Safe: counts unique orders
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.category;

Warning: Don't use SUM(DISTINCT amount) for revenue—if two orders have the same price ($19.99), one gets silently dropped. For sums, always aggregate before joining.

3. Rethink what you're measuring: Maybe you want item-level revenue, not order-level.

"Show all categories, even those with no orders":

SELECT
  c.name,
  COUNT(o.id) AS order_count,          -- 0 for empty categories
  COALESCE(SUM(o.amount), 0) AS revenue -- NULL → 0
FROM categories c
LEFT JOIN orders o ON o.category_id = c.id
GROUP BY c.name;

Key points:

• Start FROM the dimension table (categories)
• LEFT JOIN to the fact table (orders)
• Critical: Use COUNT(o.id) not COUNT(*). When there are no matches, the LEFT JOIN creates one row with all NULLs. COUNT(*) counts that NULL row (returns 1), but COUNT(o.id) ignores the NULL (returns 0).
• Use COALESCE to convert NULL sums to 0

Note: These examples use window functions (OVER), which calculate aggregates without collapsing rows. See our window functions guide for details.

Revenue by month with running total

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS monthly_revenue,
  SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date);

Conversion funnel

SELECT
  COUNT(DISTINCT user_id) AS visitors,
  COUNT(DISTINCT CASE WHEN signed_up THEN user_id END) AS signups,
  COUNT(DISTINCT CASE WHEN purchased THEN user_id END) AS buyers
FROM user_events;

Percentage breakdown

SELECT
  category,
  COUNT(*) AS count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM products
GROUP BY category;

1. Check your WHERE timing — Is a filter that should be in HAVING in WHERE, or vice versa?

2. Check for JOIN multiplication — Did a join create duplicate rows? Run SELECT COUNT(*) before and after the join.

3. Check COUNT behavior — Are you using COUNT(column) when you meant COUNT(*)? Check for NULLs.

4. Check DISTINCT — Do you need COUNT(DISTINCT x) instead of COUNT(x)?

5. Check date bucketing — Are you grouping by raw timestamp instead of DATE_TRUNC?

6. Check GROUP BY completeness — Are all non-aggregated columns in GROUP BY?

• PostgreSQL Aggregate Functions
• PostgreSQL GROUP BY and HAVING
• MySQL GROUP BY Handling