Grouping Data

GROUP BY divides rows into groups that share the same values in the grouping columns; aggregate functions are then computed per group.

Concept

GROUP BY divides rows into groups that share the same values in the grouping columns; aggregate functions are then computed per group. HAVING filters groups after aggregation.

employees (8 rows)
      │ WHERE (optional, filters rows)
      ▼
GROUP BY dept_id
 ┌────────────┬────────────┬────────────┬────────────┐
 │ dept 1     │ dept 2     │ dept 3     │ dept NULL  │
 │ 4 rows     │ 2 rows     │ 1 row      │ 1 row      │
 └────────────┴────────────┴────────────┴────────────┘
      │ aggregates computed per group
      ▼
HAVING (optional, filters groups)
      ▼
SELECT / ORDER BY

Why It Matters

"Revenue per month", "employees per department", "DTCs per ECU", "failed tests per software version" — all are GROUP BY queries.

Syntax

The GROUP BY rule: every column in SELECT must either be in GROUP BY or be inside an aggregate function (or be functionally dependent on the GROUP BY key — PostgreSQL and MySQL 5.7+ allow columns functionally dependent on a grouped primary key).

SELECT group_col1, group_col2, AGG(expr)
FROM table
[WHERE row_condition]
GROUP BY group_col1, group_col2
[HAVING group_condition]
[ORDER BY ...];

Example

12.1 GROUP BY one column (The NULL group appears last in PostgreSQL/Oracle and first in MySQL/SQL Server/SQLite.)

Grouping and NULL: all NULLs in a grouping column form one group, even though NULL = NULL is not TRUE in comparisons.

12.3 WHERE vs HAVING Filters rows Filters groups Evaluated before GROUP BY Evaluated after GROUP BY Cannot use aggregates Usually uses aggregates Can use indexes to reduce work early Works on aggregated results Works without GROUP BY

Normally used with GROUP BY (without it, the whole table is one group) Both together:

(Arjun's 90000 is not > 90000; Kiran's 80000 is excluded; so dept 3 and NULL disappear.)

Performance tip: if a condition does not involve an aggregate, put it in WHERE, not HAVING. HAVING dept_id = 1 works but filters later than WHERE dept_id = 1.

12.4 GROUP BY multiple columns One group per distinct combination of grouping values.

12.5 Grouping by an expression (monthly report) 12.6 Aggregation with conditions (conditional aggregation)

Count or sum only certain rows within each group using CASE inside the aggregate:

PostgreSQL and SQLite 3.30+ also support the standard FILTER clause:

12.7 Customers with total purchases above a threshold 12.8 Subtotals: ROLLUP, CUBE, GROUPING SETS

These produce multiple levels of aggregation in one query (supported by PostgreSQL, SQL Server, Oracle, MySQL 8 has WITH ROLLUP only; SQLite does not support them).

The row with category = NULL is the grand total. Use GROUPING(category) to distinguish a subtotal NULL from a real NULL value:

ROLLUP (a, b) → groups (a, b), (a), ().

CUBE (a, b) → groups (a, b), (a), (b), ().

GROUPING SETS ((a), (b), ()) → exactly the listed groupings.

SELECT dept_id,
       COUNT(*)     AS headcount,
       SUM(salary)  AS total_salary,
       AVG(salary)  AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 1;

SELECT dept_id, COUNT(*) AS high_earners
FROM employees
WHERE salary > 90000          -- first: keep only rows with salary > 90000
GROUP BY dept_id
HAVING COUNT(*) >= 1          -- then: keep groups with at least one such employee
ORDER BY dept_id;

SELECT o.customer_id, o.status, COUNT(*) AS n_orders, SUM(o.total_amount) AS amount
FROM orders o
GROUP BY o.customer_id, o.status
ORDER BY o.customer_id, o.status;

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date)::DATE AS month,
       COUNT(*) AS orders,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- MySQL:      GROUP BY DATE_FORMAT(order_date, '%Y-%m')
-- SQL Server: GROUP BY YEAR(order_date), MONTH(order_date)
-- SQLite:     GROUP BY strftime('%Y-%m', order_date)

SELECT EXTRACT(MONTH FROM order_date) AS month_no,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status <> 'CANCELLED' THEN total_amount ELSE 0 END) AS net_revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month_no;

SELECT COUNT(*) FILTER (WHERE status = 'DELIVERED') AS delivered FROM orders;

SELECT customer_id, COUNT(*) AS n_orders, SUM(total_amount) AS lifetime_value
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
HAVING SUM(total_amount) > 50000
ORDER BY lifetime_value DESC;

-- PostgreSQL, SQL Server, Oracle
SELECT category, COUNT(*) AS n_products, SUM(stock) AS units
FROM products
GROUP BY ROLLUP (category)
ORDER BY category;
-- MySQL: GROUP BY category WITH ROLLUP

SELECT CASE WHEN GROUPING(category) = 1 THEN 'ALL CATEGORIES' ELSE category END AS category,
       SUM(stock) AS units
FROM products
GROUP BY ROLLUP (category);

Expected Result

Category statistics:

SELECT category,
       COUNT(*)              AS n_products,
       ROUND(AVG(price), 2)  AS avg_price,
       MAX(price)            AS max_price
FROM products
GROUP BY category
ORDER BY category;

Common Mistakes

Selecting a non-grouped, non-aggregated column: SELECT dept_id, first_name, COUNT(*) FROM employees GROUP BY dept_id; → error ("column must appear in the GROUP BY clause or be used in an aggregate function").

Filtering aggregates in WHERE instead of HAVING.

Putting non-aggregate filters in HAVING (works, but slower).

Grouping by a formatted string (e.g., 'Jan 2026') and then sorting alphabetically instead of chronologically.

Forgetting that NULLs form their own group.

Relying on MySQL's permissive GROUP BY (with ONLY_FULL_GROUP_BY off) which returns arbitrary values.

Best Practices

Group by keys (IDs), then join to get names — or include both ID and name in GROUP BY.

Use conditional aggregation for pivot-style reports.

Keep WHERE for row filters, HAVING for aggregate filters.

Always ORDER BY grouped results explicitly; GROUP BY does not guarantee order.

Interview Relevance

Very high: "WHERE vs HAVING?", "Find departments with more than N employees", "Find customers whose total purchase > X", "Monthly revenue report", "Can you use HAVING without GROUP BY?" (Yes — the whole result is one group.)

Practice Questions

Count products per category with total stock value (price * stock).

Find categories whose average price exceeds 20000.

Count orders per status.

For each customer, show number of orders and the date of their most recent order.

Show the number of employees hired per year.

Produce a report with one row per month and separate columns for DELIVERED, SHIPPED, PENDING, CANCELLED counts.

Quiz Checkpoint 2 (Modules 6 – 12)

Q1. (Multiple choice) In which clause can you use an aggregate function to filter results? A) WHERE B) HAVING C) FROM D) ON

Answer: B. WHERE is evaluated before grouping.

Q2. (Predict the output) Answer: 8, 7, 3.

Q3. (True/False) SELECT DISTINCT a, b removes rows that have duplicate values in column a only.

Answer: False. DISTINCT applies to the combination of all selected columns.

Q4. (Find the error) Answer: order_date is neither grouped nor aggregated. Use MAX(order_date) or add it to GROUP BY.

Q5. (Predict the output) Answer: No rows. A NULL in a NOT IN list makes every comparison UNKNOWN or FALSE.

Q6. (Write the query) Show the 3 customers with the highest total non-cancelled order value.

Result: customer 2 (105000), customer 1 (93900), customer 4 (1600).

Q7. (Multiple choice) What does SELECT SUM(total_amount) FROM orders WHERE 1 = 0; return? A) 0 B) NULL C) Error D) Empty string

Answer: B. SUM over no rows is NULL.

Q8. (Predict the output) In PostgreSQL, what is SELECT 9 / 2, 9 % 2, ROUND(9 / 2.0);?

Answer: 4, 1, 5. Integer division gives 4; remainder 1; 4.5 rounds half away from zero to 5.

Q9. (Find the error) Answer: The alias gross does not exist when WHERE is evaluated. Use WHERE price * 1.18 > 10000.

Q10. (True/False) ORDER BY without LIMIT guarantees that rows with equal sort values come back in insertion order.

Answer: False. The order of ties is undefined.

SELECT COUNT(*), COUNT(dept_id), COUNT(DISTINCT dept_id) FROM employees;

SELECT customer_id, order_date, SUM(total_amount)
FROM orders
GROUP BY customer_id;

SELECT first_name FROM employees WHERE dept_id NOT IN (2, 3, NULL);

SELECT customer_id, SUM(total_amount) AS total
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 3;            -- SQL Server: SELECT TOP (3) ...; Oracle: FETCH FIRST 3 ROWS ONLY

SELECT product_name, price * 1.18 AS gross
FROM products
WHERE gross > 10000;