SQL Aggregations — GROUP BY & HAVING

0. The Sample Tables

We continue with customers and add a small transactions table for the HAVING examples.

1. Aggregate Functions — an Overview

Aggregate functions collapse many rows into a single summary value. They answer "how many?", "how much in total?", "what's the average?".

-- How many customers in total?
SELECT COUNT(*) AS total_customers
FROM customers;

-- What is the total balance across all customers?
SELECT SUM(balance) AS total_balance
FROM customers;

-- What is the average balance?
SELECT AVG(balance) AS avg_balance
FROM customers;

2. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

This is one of the most common interview traps in SQL. The three forms count different things:

-- Count all customers vs distinct cities
SELECT COUNT(*)              AS total_rows,       -- 6
       COUNT(city)           AS non_null_cities,  -- 6 (no NULLs here)
       COUNT(DISTINCT city)  AS unique_cities     -- 4 (Mumbai, Delhi, Bengaluru, Chennai)
FROM customers;

3. GROUP BY — Summarize by Category

GROUP BY splits the table into groups (one per unique value) and runs the aggregate function within each group. It's the SQL equivalent of an Excel Pivot Table.

-- Total balance per city
SELECT city, SUM(balance) AS total_balance
FROM customers
GROUP BY city
ORDER BY total_balance DESC;

-- Number of customers per account type
SELECT account_type, COUNT(*) AS customer_count
FROM customers
GROUP BY account_type;

4. The GROUP BY Trap (Read This Carefully)

The golden rule: every non-aggregate column in your SELECT must also appear in GROUP BY. Violating this causes an error in PostgreSQL/SQL Server/modern MySQL — or worse, silently wrong results in older MySQL.

-- ❌ WRONG: 'customer_name' is selected but not in GROUP BY
SELECT customer_id, customer_name, SUM(balance)
FROM customers
GROUP BY customer_id;

-- ✅ CORRECT: every non-aggregate column is grouped
SELECT customer_id, customer_name, SUM(balance) AS total_balance
FROM customers
GROUP BY customer_id, customer_name;

5. HAVING vs WHERE

The single most asked aggregation question: WHERE filters rows before grouping; HAVING filters groups after grouping.

-- Cities where the total balance is above 40,000
SELECT city, SUM(balance) AS total_balance
FROM customers
GROUP BY city
HAVING SUM(balance) > 40000;

Why can't we use WHERE SUM(balance) > 40000? Because SUM doesn't exist yet when WHERE runs — aggregation happens after WHERE.

A full example: customers with more than one transaction

SELECT customer_id, COUNT(*) AS txn_count
FROM transactions
WHERE amount > 100          -- row filter (before grouping)
GROUP BY customer_id
HAVING COUNT(*) > 1         -- group filter (after grouping)
ORDER BY txn_count DESC;

This returns customer 1 (3 transactions). Note how WHERE and HAVING work at different stages.

6. MIN, MAX, and How Aggregates Treat NULL

MIN and MAX

-- Smallest and largest balance per account type
SELECT account_type, MIN(balance) AS min_balance, MAX(balance) AS max_balance
FROM customers
GROUP BY account_type;

How NULL affects each aggregate