GROUP BY

Introduction

GROUP BY partitions rows into groups so aggregates apply per group. Every SELECT column must either be in GROUP BY or wrapped in an aggregate.

Understanding the topic

Core concepts to understand:

• GROUP BY country → one row per country.
• GROUP BY country, plan → one row per (country, plan).
• Logical order: WHERE → GROUP BY → HAVING → SELECT.
• Use ROLLUP/CUBE for subtotals (advanced).
• Index helps if cardinality is high.

Syntax reference

Visual workflow / architecture:

rows
  └─ WHERE filter
       └─ GROUP BY country
            ├─ US  ── SUM/COUNT
            ├─ UK  ── SUM/COUNT
            └─ IN  ── SUM/COUNT
                 └─ HAVING filter
                      └─ SELECT projection

SELECT <group_col>, <agg_func>(<col>)
FROM   <table>
[WHERE <row_filter>]
GROUP  BY <group_col>;

SELECT plan, COUNT(*) AS users
FROM   users
GROUP  BY plan;

Real-world use

Reports like 'orders per country', 'sign-ups per day', 'errors per service' are GROUP BY queries.

Best practices

• Group only by indexed/low-cardinality columns when possible.
• Always understand the SELECT/GROUP BY rule — no naked columns.
• Use HAVING to filter post-aggregation, WHERE for pre.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why must non-aggregated SELECT cols be in GROUP BY?
• Q2. Difference between WHERE and HAVING.
• Q3. ROLLUP / CUBE — what do they do?
• Q4. How to count distinct per group?
• Q5. Effect of indexes on GROUP BY.