GROUP BY

By the end of this lesson, you will be able to aggregate data by category using SQL GROUP BY to calculate summary statistics like totals and averages for each distinct group.

What it is

GROUP BY is a SQL clause that arranges identical values into summary rows. It works in conjunction with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN(). The mental model is sorting your data into buckets based on a specific column (the category), then calculating a single result for each bucket. Related terms include "aggregation," "dimension" (the grouping column), and "measure" (the aggregated value).

Why it matters

• Business Intelligence: Enables reporting metrics like total sales per region or average user age per subscription tier.
• Data Reduction: Condenses thousands of transactional rows into a manageable number of summary rows.
• Trend Analysis: Allows comparison of performance across different categories over time.
• Efficiency: Performs calculations within the database engine rather than transferring raw data to an application layer.

Syntax or steps

The basic structure requires selecting the grouping column(s) and the aggregate function(s). You must list every non-aggregated column from the SELECT statement in the GROUP BY clause.

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Example

Imagine a table named orders containing columns: order_id, customer_region, and amount. We want to find the total revenue and number of orders for each region.

SELECT 
    customer_region, 
    COUNT(order_id) AS total_orders, 
    SUM(amount) AS total_revenue
FROM 
    orders
GROUP BY 
    customer_region;

Explanation:

1. SELECT customer_region: Identifies the category we are grouping by.
2. COUNT(order_id): Calculates how many rows exist in each region group.
3. SUM(amount): Adds up all monetary values within each region group.
4. FROM orders: Specifies the source table.
5. GROUP BY customer_region: Instructs the database to collapse rows sharing the same region into one output row.

Common mistakes

• Selecting ungrouped columns: Including a column in SELECT that is not in GROUP BY nor inside an aggregate function causes errors in strict SQL modes (e.g., PostgreSQL, SQL Server). Fix: Add the column to GROUP BY or wrap it in an aggregate like MAX().
• Confusing WHERE and HAVING: Using WHERE to filter after aggregation fails because WHERE runs before grouping. Fix: Use HAVING to filter groups (e.g., HAVING SUM(amount) > 1000).
• Forgetting NULL handling: Rows with NULL in the grouping column form their own group. Fix: Decide if NULLs should be excluded via WHERE column IS NOT NULL or treated as a valid category.
• Misinterpreting order: GROUP BY does not guarantee sorted output. Fix: Always add ORDER BY if display order matters.

When to use it

Compare GROUP BY with simple filtering (WHERE) or window functions.

Practice

Guided Exercise: Given a table employees with columns department and salary, write a query to find the average salary for each department.

Challenge: Modify the query above to only show departments where the average salary is greater than 50,000.

Solution Hint: Use AVG(salary) in the select list and HAVING AVG(salary) > 50000 at the end.

Quick check

Question: Why can't you use WHERE SUM(amount) > 100 to filter groups?

Answer: Because WHERE filters individual rows before they are grouped. Aggregates like SUM() do not exist yet during the WHERE phase. You must use HAVING instead.

Summary

GROUP BY transforms detailed transactional data into categorical insights by collapsing rows based on shared values. Mastering its interaction with aggregate functions and the HAVING clause is essential for effective data analysis and reporting.