SQL-Engineering-Handbook

01 · Advanced GROUP BY

🏠 Module Home · 🗂️ Handbook Home · Next → 02 Multiple Aggregations

Advanced GROUP BY

Module: 02 — Advanced Aggregations Domain used in this file: Human Resources (employees, departments, locations) Companion file: 01_ADVANCED_GROUP_BY.sql

Introduction

A single-column GROUP BY answers one question: “what’s the total per category?” The moment a stakeholder asks for a total per category, broken down by a second category, you need multi-column and nested grouping — the subject of this file.

This is the single most common upgrade a beginner analyst has to make: moving from “total sales per region” to “total sales per region, per month, per product category.” The SQL mechanism barely changes. What changes is your responsibility to reason correctly about grain — exactly what one output row represents.

Concept Overview

GROUP BY col1, col2, ... collapses rows into groups defined by the unique combination of every listed column — not each column independently. GROUP BY department, city does not produce “one group per department plus one group per city.” It produces one group per (department, city) pair that actually exists in the data.

This combination is called the grain of the result set. Every aggregate function in the SELECT list (COUNT, SUM, AVG, etc.) is computed within that grain, one value per group.

Business Motivation

An HR analytics team is asked: “Give me headcount by department, and also tell me how that breaks down by office location.” A single-column GROUP BY department cannot answer the location part. A single-column GROUP BY location cannot answer the department part. Only grouping by both columns together produces a table where each row is a legitimate, addressable business unit: “8 people in Engineering, in the Nagpur office.”

This is the pattern behind virtually every cross-tabulated business report: revenue by region and quarter, tickets by severity and team, orders by channel and payment method.

Why This Feature Exists

Relational databases store data in its most granular, transactional form — one row per employee, one row per order line. Businesses almost never think at that grain; they think in aggregates, at whatever combination of dimensions matters for the decision being made. GROUP BY with multiple columns is the mechanism that lets the same underlying table answer questions at many different levels of granularity, without needing a separate pre-built table for every possible breakdown.

Real Company Examples

Business Problems Solved

Visual Explanation

Grouping by a single column collapses rows into one bucket per value:

GROUP BY department
┌─────────────┐        ┌───────────────────────┐
│ Engineering │──┐     │ Engineering  →  8 rows │
│ Engineering │──┼──▶  └───────────────────────┘
│ Engineering │──┘     ┌───────────────────────┐
│ Sales       │──┐     │ Sales        →  5 rows │
│ Sales       │──┘     └───────────────────────┘

Grouping by two columns collapses rows into one bucket per combination:

GROUP BY department, city
┌─────────────────────────┐    ┌─────────────────────────────────┐
│ Engineering | Nagpur     │──┐ │ Engineering, Nagpur   →  5 rows │
│ Engineering | Nagpur     │──┘ └─────────────────────────────────┘
│ Engineering | Pune       │──┐ │ Engineering, Pune     →  3 rows │
│ Engineering | Pune       │──┘ └─────────────────────────────────┘
│ Sales       | Nagpur     │──┐ │ Sales, Nagpur         →  5 rows │
│ Sales       | Nagpur     │──┘ └─────────────────────────────────┘

Notice the grain got finer — more, smaller groups — the moment a second column was added.

Syntax

SELECT
    col1,
    col2,
    AGG_FUNCTION(col3) AS metric_alias
FROM table_name
GROUP BY col1, col2
[HAVING aggregate_condition]
[ORDER BY col1, col2];

Rule: every column in SELECT that is not wrapped in an aggregate function must appear in GROUP BY. This applies in strict SQL modes (PostgreSQL always; MySQL under ONLY_FULL_GROUP_BY, which is the default since MySQL 5.7).

Detailed Walkthrough

SELECT
    d.dept_name,
    l.city,
    COUNT(DISTINCT e.emp_id) AS total_employees
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id
JOIN locations  AS l ON d.location_id = l.location_id
GROUP BY d.dept_name, l.city
ORDER BY d.dept_name, l.city;
  1. The FROM/JOIN clauses build the full detail row set — one row per employee, carrying their department and city.
  2. GROUP BY d.dept_name, l.city collapses that detail set into one row per (department, city) combination actually present in the data.
  3. COUNT(DISTINCT e.emp_id) computes the metric within each group.
  4. ORDER BY is applied last, after aggregation, to sort the final report — not the raw rows.

Production Workflow

Multi-column GROUP BY queries typically sit in a scheduled reporting job or a dbt model: raw HR/transactional tables are joined and grouped at a fixed grain, the result is materialized into a summary table, and the BI layer queries that summary table instead of re-aggregating the full history on every dashboard load.

Analytics Engineering Perspective

Performance Considerations

Edge Cases

Common Mistakes

Best Practices

Interview Questions

  1. What determines a “group” when GROUP BY lists more than one column? The unique combination of values across all listed columns — not each column independently.
  2. Why might COUNT(emp_id) return a higher number than the actual headcount after a join? A one-to-many join (e.g., to a table with multiple rows per employee) duplicates employee rows before aggregation; COUNT(DISTINCT emp_id) corrects this.
  3. What happens to rows with a NULL value in a GROUP BY column? They form their own group under NULL, rather than being excluded or erroring.
  4. Why does adding a second GROUP BY column typically increase the number of output rows? It makes the grain finer — you’re now grouping by combinations rather than single values, which almost always produces more, smaller groups.
  5. How would you get a department to appear with zero headcount in a given city, if no employees currently match? GROUP BY alone won’t show it; you need an outer join against a complete dimension table of valid department/city combinations, with COALESCE(COUNT(...), 0).

Summary

Multi-column GROUP BY is single-column GROUP BY applied to a combination of dimensions instead of one. The mechanism is identical; the responsibility that grows is reasoning correctly about grain — what one row of your output actually represents — especially once joins are involved and row counts can silently inflate before aggregation ever runs.

Practice Challenges

  1. Write a query returning employee count per department and per manager, in one result set.
  2. Extend the department/city report in this file to also show the earliest hire date per (department, city) combination.
  3. Find every (department, city) combination with zero employees, using an outer join against a full list of valid combinations.
  4. Rewrite the “highest headcount department” scenario in the companion SQL file without a subquery, using only ORDER BY and LIMIT, and explain the tradeoff versus the subquery version.
  5. Produce a report of employee count by department, city, and whether the employee has a manager — three grouping dimensions in one query.

Further Reading

◀ Previous: Module README · Next ▶ 02_MULTIPLE_AGGREGATIONS.md

⬆ Back to top · 🏠 Module Home · 🗂️ Handbook Home

This site is open source. Improve this page.