KAHIBARO
Discord Login Register ☰
☰

10.9. GROUP BY

Table of Contents

Why `GROUP BY` Matters

When you work with data, you often need to answer questions like:

All these questions require grouping rows that share some value and then applying aggregate functions (like COUNT, SUM, AVG) on each group.

That is exactly what GROUP BY does.

Rule: Use GROUP BY whenever you want to apply aggregate functions per group of rows, not just over the entire table.

This chapter focuses only on GROUP BY. Aggregate functions themselves (like COUNT, SUM, AVG) are covered in the Aggregate Functions chapter, but we will use them in examples here.

Basic `GROUP BY` Syntax

The logical structure looks like this:

sql 
SELECT
    column_or_expression,
    aggregate_function(...)
FROM table_name
GROUP BY column_or_expression;

The idea:

  1. GROUP BY splits the rows from FROM into groups with the same values in the group-by columns.
  2. Aggregate functions calculate one result per group.
  3. The SELECT returns one row per group.

Simple Example: Counting rows per value

Table: orders

idcustomer_idstatustotal_amount
110'paid'100.00
210'paid'50.00
311'unpaid'70.00
412'paid'120.00

Count how many orders each customer has:

sql 
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Result:

customer_idorder_count
102
111
121

Here, GROUP BY customer_id creates 3 groups (customer 10, 11, 12). COUNT(*) runs once per group.

Rules for Columns in `SELECT` with `GROUP BY`

This is the part that often confuses beginners.

Very important rule:
In a SELECT with GROUP BY:

  • Every column or expression in the SELECT must be:
    • either listed in the GROUP BY clause
    • or wrapped in an aggregate function (like COUNT, SUM, MIN, MAX, AVG, etc.)

Valid vs invalid examples

Using orders table again.

✅ Valid:

sql 
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

❌ Invalid:

sql 
SELECT
    customer_id,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Problem: status is in SELECT but not in GROUP BY and not aggregated. The database does not know which status to show for each customer_id, since a customer could have multiple statuses across orders.

To fix it, either:

  1. Add status to the grouping:
sql 
SELECT
    customer_id,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

Now each group is a combination of (customer_id, status).

Result example:

customer_idstatusorder_count
10'paid'2
11'unpaid'1
12'paid'1
  1. Or aggregate status, for example, with MIN(status) or MAX(status):
sql 
SELECT
    customer_id,
    MIN(status) AS some_status,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Now MIN(status) chooses one status per customer according to sorting rules. Whether this makes sense depends on the data and the question you are answering.

Grouping by Multiple Columns

You are not limited to a single column. You can group by multiple columns to create more detailed groups.

Example: Sales per customer per status

sql 
SELECT
    customer_id,
    status,
    COUNT(*)      AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id, status;

Here the groups are defined by both customer_id and status. For each unique pair of (customer_id, status) you get one row.

With our sample data:

idcustomer_idstatustotal_amount
110'paid'100.00
210'paid'50.00
311'unpaid'70.00
412'paid'120.00

Result:

customer_idstatusorder_counttotal_spent
10'paid'2150.00
11'unpaid'170.00
12'paid'1120.00

Grouping by expressions

You can group by calculated expressions too.

Suppose orders has a created_at column of type timestamp.

idcustomer_idtotal_amountcreated_at
110100.002024-01-01 10:15:00
21050.002024-01-01 11:00:00
31170.002024-01-02 09:30:00

Group by date only, ignoring time:

sql 
SELECT
    DATE(created_at) AS order_date,
    COUNT(*)         AS orders_on_date,
    SUM(total_amount) AS total_on_date
FROM orders
GROUP BY DATE(created_at);

Result:

order_dateorders_on_datetotal_on_date
2024-01-012150.00
2024-01-02170.00

Note that the expression DATE(created_at) appears both in SELECT and GROUP BY.

`GROUP BY` and `WHERE`: Filtering Before Grouping

Often you do not want to group all rows, only some of them. Use WHERE to filter rows before grouping.

Order of operations (simplified):

  1. FROM and JOIN
  2. WHERE (filters individual rows)
  3. GROUP BY (groups remaining rows)
  4. HAVING (filters groups, covered later)
  5. SELECT
  6. ORDER BY

Example: Count only paid orders per customer

sql 
SELECT
    customer_id,
    COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

Here:

If the original table was:

idcustomer_idstatus
110'paid'
210'paid'
310'unpaid'
411'paid'
512'unpaid'

After WHERE status = 'paid', remaining rows:

idcustomer_idstatus
110'paid'
210'paid'
411'paid'

Then grouping and counting gives:

customer_idpaid_orders
102
111

`GROUP BY` with `HAVING`: Filtering After Grouping

Sometimes you want to filter groups, not individual rows. For example, "only customers with more than 3 orders".

You cannot do that with WHERE, because WHERE does not know about group results like COUNT(*).

For that, SQL has the HAVING clause. The Aggregate Functions chapter will discuss this more deeply, but here is the basic idea connected to GROUP BY.

Basic pattern with `HAVING`

sql 
SELECT
    group_column,
    aggregate_function(...) AS agg
FROM table_name
GROUP BY group_column
HAVING aggregate_function(...) condition;

Example: Customers with at least 2 orders

sql 
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Interpretation:

Given this data:

idcustomer_id
110
210
311
412
512

Result:

customer_idorder_count
102
122

Combining `WHERE` and `HAVING`

You can and often should use both.

Example: Find customers who have at least 2 paid orders.

sql 
SELECT
    customer_id,
    COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'              -- filter rows first
GROUP BY customer_id
HAVING COUNT(*) >= 2;              -- then filter groups

Practical Examples of `GROUP BY`

Let us look at different question types and how GROUP BY helps.

Example 1: Counting distinct values

Table: users

idcountryage
1'US'22
2'US'30
3'DE'25
4'FR'27

Question: How many users per country?

sql 
SELECT
    country,
    COUNT(*) AS user_count
FROM users
GROUP BY country;

Result:

countryuser_count
'US'2
'DE'1
'FR'1

Example 2: Average per group

Question: What is the average age per country?

sql 
SELECT
    country,
    AVG(age) AS avg_age
FROM users
GROUP BY country;

Result:

countryavg_age
'US'26.0
'DE'25.0
'FR'27.0

Example 3: Grouping by multiple columns

Table: events

iduser_idevent_typecreated_at
11'login'2024-01-01 10:00:00
21'click'2024-01-01 10:05:00
32'login'2024-01-01 11:00:00
41'login'2024-01-02 09:00:00

Question: How many events per user and per event type?

sql 
SELECT
    user_id,
    event_type,
    COUNT(*) AS event_count
FROM events
GROUP BY user_id, event_type;

Result:

user_idevent_typeevent_count
1'login'2
1'click'1
2'login'1

Example 4: Grouping by a transformed value

Question: How many logins per day?

sql 
SELECT
    DATE(created_at) AS login_date,
    COUNT(*) AS login_count
FROM events
WHERE event_type = 'login'
GROUP BY DATE(created_at)
ORDER BY login_date;

If the timestamps are:

idevent_typecreated_at
1'login'2024-01-01 10:00:00
2'login'2024-01-01 11:00:00
3'login'2024-01-02 09:00:00

Result:

login_datelogin_count
2024-01-012
2024-01-021

Common Pitfalls and How to Avoid Them

Pitfall 1: Selecting non grouped, non aggregated columns

You might try:

sql 
SELECT
    customer_id,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

This is usually not allowed (or it gives undefined results in some databases) because status is neither grouped nor aggregated.

Fix: Either group by status too, or aggregate it.

sql 
-- Option 1
GROUP BY customer_id, status;
-- Option 2
SELECT
    customer_id,
    MIN(status) AS some_status,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Pitfall 2: Using `WHERE` for group conditions

You cannot write:

sql 
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer_id;

WHERE cannot use COUNT(*) because grouping is not done yet.

Fix: Use HAVING instead.

sql 
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Pitfall 3: Forgetting to group by all non aggregated columns

When you have more than one non aggregated column, all of them must appear in the GROUP BY.

❌ Wrong:

sql 
SELECT
    country,
    city,
    COUNT(*) AS user_count
FROM users
GROUP BY country;

✅ Correct:

sql 
SELECT
    country,
    city,
    COUNT(*) AS user_count
FROM users
GROUP BY country, city;

Mental Model for `GROUP BY`

It helps to imagine GROUP BY in steps:

  1. Start with all rows from FROM and WHERE.
  2. Place each row into a bucket based on the group-by column values.
    • Group by country means one bucket per country.
    • Group by country, city means one bucket per pair of values.
  3. For each bucket, compute aggregate functions using only rows in that bucket.
  4. Output one row per bucket.

For example, GROUP BY country:

COUNT(*) counts how many rows are in each bucket. SUM(amount) adds amounts in each bucket. AVG(age) averages ages in each bucket.

Summary

Understanding GROUP BY is essential for any backend developer who reads or writes SQL for reporting, analytics, or even simple statistics in APIs.

Views: 9

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3085 / 24h
Impressum / Imprint
Datenschutz / Data Policy