SQL GROUP BY for Grouping Results

In the previous guide, you learned how aggregate functions condense an entire table into a single summary row. COUNT(*) tells you how many total products you have. AVG(price) gives you one average across all products. But what if you need the average price per category? Or the number of orders per customer? Or the total revenue per month?

These questions require the database to split the data into groups first, then aggregate each group separately. This is exactly what GROUP BY does. It is one of the most powerful clauses in SQL and the foundation of every meaningful report, dashboard, and analytical query.

This guide covers how GROUP BY works conceptually, how to group by one or multiple columns, the critical error that every beginner encounters (selecting non-aggregated columns), and practical real-world examples. Everything uses the ShopSmart sample database with full outputs (we defined it in a previous guide here).

How GROUP BY Works Conceptually

Without GROUP BY, aggregate functions process all matching rows as one big group and return a single result:

-- One group, one result: the average price across ALL products
SELECT AVG(price) AS avg_price
FROM products;

With GROUP BY, the database first divides the rows into groups based on the values in one or more columns, then applies the aggregate function to each group separately, returning one result row per group:

-- Multiple groups, one result per group: average price PER category
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

Instead of one average for all products, you now have a separate average for each category.

The Mental Model

Think of GROUP BY as a three-step process:

Step 1: Sort rows into groups based on the GROUP BY column:

Step 2: Apply the aggregate function to each group independently:

• Category 1: AVG(29.99, 89.99, 45.00, 65.00) = 57.50
• Category 2: AVG(34.99, 42.50) = 38.75
• Category 3: AVG(129.99) = 129.99
• Category 4: AVG(38.00, 110.00, 24.99) = 57.66

Step 3: Return one row per group with the group identifier and the aggregated value.

Grouping by One Column

The most common pattern is grouping by a single column to get summaries per category, per status, per customer, or per any other dimension.

Products Per Category

SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY product_count DESC;

Output:

Electronics (category 1) has the most products. Home & Kitchen (category 3) has only one.

Orders Per Status

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

Output:

Revenue Per Status

SELECT status,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

Output:

Each status group gets its own count, total, and average. This single query could power an order management dashboard.

Orders Per Customer

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

Output:

Customer 1 (Alice) has spent the most overall with 2 orders totaling $197.47. Customer 6 (Frank) has the highest single order at $199.98.

Customers Per City

SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC;

Output:

Notice the WHERE city IS NOT NULL filter. Without it, NULL would form its own group. Whether that is desirable depends on your use case.

What Happens to NULL in GROUP BY?

Unlike most SQL operations where NULL creates confusion, GROUP BY treats all NULL values as belonging to the same group:

-- NULL forms its own group
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;

Output:

Grace's NULL city creates a group of its own. This is consistent with how DISTINCT treats NULL values.

Grouping by Multiple Columns

You can group by two or more columns to create more specific groupings. The database creates a separate group for each unique combination of values across all grouped columns.

Orders Per Customer Per Status

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

Output:

Customer 2 (Bob) appears in two rows because he has orders in two different statuses. Each unique combination of customer_id and status gets its own group.

Compare this with the single-column grouping:

-- Single-column: 6 groups (one per customer)
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
-- Returns 6 rows

-- Multi-column: 7 groups (one per customer-status combination)
SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;
-- Returns 7 rows (Bob has 2 rows now)

Products Per Category and Availability

SELECT category_id,
       is_available,
       COUNT(*) AS product_count,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id, is_available
ORDER BY category_id, is_available DESC;

Output:

Category 1 (Electronics) splits into two groups: 3 available products averaging $54.99 and 1 unavailable product at $65.00 (the Bluetooth Speaker).

Orders Per Month and Status

-- Extract year-month from order dates for monthly grouping
-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month,
       status,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM'), status
ORDER BY order_month, status;

Output:

Adding Context with JOINs

Grouping by category_id or customer_id produces correct numbers, but raw IDs are not very readable. Joining with related tables lets you display names alongside the aggregated data.

Product Counts with Category Names

SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       MIN(p.price) AS cheapest,
       MAX(p.price) AS most_expensive
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.name
ORDER BY product_count DESC;

Output:

Now the report shows "Electronics" instead of "1", which is far more useful for anyone reading it.

Customer Order Summary with Names

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.city,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_spent,
       COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC;

Output:

The LEFT JOIN ensures Grace appears even though she has no orders. COALESCE converts her NULL totals to 0.

The Most Common GROUP BY Error

This is the single most important section of this guide. Almost every SQL beginner encounters this error, and understanding why it happens will permanently improve your SQL writing.

The Error

-- This query FAILS in most databases
SELECT name, category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

Error message (PostgreSQL):

ERROR: column "products.name" must appear in the GROUP BY clause
or be used in an aggregate function

Error message (SQL Server):

Column 'products.name' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

Why It Fails

Let us trace through the logic. When you write GROUP BY category_id, the database creates one group per category:

For category 1, AVG(price) produces one number: 57.50. But name has four different values in this group. Which name should the database display? "Wireless Mouse"? "Mechanical Keyboard"? There is no logical answer. The database refuses to guess and raises an error instead.

The Rule

Every column in your SELECT list must either:

1. Appear in the GROUP BY clause, or
2. Be used inside an aggregate function (COUNT, SUM, AVG, MIN, MAX)

No exceptions (with one caveat for MySQL, discussed below).

How to Fix It

There are several valid approaches depending on what you actually need:

Fix 1: Remove the non-aggregated column

-- If you just want the average price per category, you don't need the name
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

Fix 2: Add the column to GROUP BY

-- This creates a group for each unique (category_id, name) combination
-- Probably NOT what you want, as each product becomes its own group
SELECT name, category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id, name;

Each product is now its own group, and the "average" is just each product's own price. This is technically valid but defeats the purpose of aggregation.

Fix 3: Aggregate the column

-- Use MIN or MAX on the name column if you want a representative name
SELECT MIN(name) AS first_product_name,
       category_id,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id;

Fix 4: Use a JOIN or subquery to bring in non-grouped data

This is the most robust approach when you need detailed information alongside grouped summaries, but it requires more advanced techniques covered in future guides.

Multiple Wrong Examples and Their Fixes

-- Wrong: 'email' is not grouped or aggregated
SELECT city, email, COUNT(*) AS customer_count
FROM customers
GROUP BY city;
-- ERROR

-- Fix: Remove email or aggregate it
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city;

-- Wrong: 'order_date' is not grouped or aggregated
SELECT customer_id, order_date, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;
-- ERROR

-- Fix option 1: Add order_date to GROUP BY (groups by customer AND date)
SELECT customer_id, order_date, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id, order_date;

-- Fix option 2: Aggregate order_date
SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

GROUP BY with WHERE

WHERE filters rows before grouping. The database removes rows that do not match the WHERE condition, then groups the remaining rows.

-- Count available products per category
SELECT category_id, COUNT(*) AS available_count
FROM products
WHERE is_available = true
GROUP BY category_id
ORDER BY available_count DESC;

Output:

Compare this with the unfiltered count:

-- Count ALL products per category (including unavailable)
SELECT category_id, COUNT(*) AS total_count
FROM products
GROUP BY category_id
ORDER BY total_count DESC;

Category 1 drops from 4 to 3 because the Bluetooth Speaker (unavailable) is excluded by WHERE.

Revenue from Completed Orders Per Customer

SELECT customer_id,
       COUNT(*) AS completed_orders,
       SUM(total_amount) AS completed_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY completed_revenue DESC;

Output:

Only Alice and Bob have completed orders.

The Processing Order

Understanding the order in which SQL processes clauses is critical when using GROUP BY:

This order explains several important behaviors:

• WHERE cannot use aggregate functions (aggregation has not happened yet at step 2)
• WHERE cannot use column aliases (aliases are not created until step 5)
• ORDER BY can use aliases (it runs after SELECT)
• GROUP BY cannot use column aliases in most databases (it runs before SELECT)

GROUP BY with ORDER BY

You can sort grouped results just like any other query. Sorting by the aggregate result is particularly useful:

Categories Ranked by Average Price

SELECT category_id,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id
ORDER BY avg_price DESC;

Output:

Customers Ranked by Total Spending

SELECT customer_id,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Output:

Top 3 Categories by Product Count

Combining GROUP BY, ORDER BY, and LIMIT:

SELECT category_id,
       COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY product_count DESC
LIMIT 3;

Output:

Real-World GROUP BY Scenarios

Sales Report by Month

-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS orders,
       SUM(total_amount) AS revenue,
       ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

Output:

Inventory Summary by Category

SELECT c.name AS category,
       COUNT(p.id) AS products,
       SUM(p.stock_quantity) AS total_stock,
       ROUND(SUM(p.price * p.stock_quantity), 2) AS inventory_value
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.name
ORDER BY inventory_value DESC;

Output:

Price Distribution Analysis

SELECT CASE
           WHEN price < 30 THEN 'Under $30'
           WHEN price < 50 THEN '$30 - $49.99'
           WHEN price < 100 THEN '$50 - $99.99'
           ELSE '$100 and above'
       END AS price_range,
       COUNT(*) AS product_count,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY CASE
             WHEN price < 30 THEN 'Under $30'
             WHEN price < 50 THEN '$30 - $49.99'
             WHEN price < 100 THEN '$50 - $99.99'
             ELSE '$100 and above'
         END
ORDER BY avg_price;

Output:

Order Activity by Day of Week

-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'Day') AS day_of_week,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'Day'), EXTRACT(DOW FROM order_date)
ORDER BY EXTRACT(DOW FROM order_date);

This kind of query helps businesses understand their busiest days.

Common GROUP BY Mistakes

Mistake 1: Forgetting GROUP BY with Aggregates

-- Wrong: Aggregate mixed with non-aggregated column, no GROUP BY
SELECT category_id, COUNT(*)
FROM products;
-- ERROR: column "products.category_id" must appear in the GROUP BY clause

Fix:

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;

Mistake 2: Using WHERE Instead of HAVING to Filter Groups

-- Wrong: WHERE cannot filter on aggregate results
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;
-- ERROR: aggregate functions are not allowed in WHERE

WHERE filters individual rows before grouping. To filter after grouping based on aggregate results, you need HAVING (covered in the next guide). For now, here is a preview:

-- Correct: HAVING filters groups after aggregation
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

Mistake 3: Selecting Too Many Columns

-- Wrong: price, name, and stock_quantity are not in GROUP BY
SELECT category_id, name, price, stock_quantity, COUNT(*)
FROM products
GROUP BY category_id;
-- ERROR

Fix: Only select what you can aggregate or group:

SELECT category_id,
       COUNT(*) AS product_count,
       MIN(price) AS cheapest,
       MAX(price) AS most_expensive,
       SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category_id;

Output:

Mistake 4: Grouping Defeats the Purpose

-- Technically valid, but pointless: every row is its own group
SELECT id, name, price, COUNT(*) AS count
FROM products
GROUP BY id, name, price;

If you group by the primary key (or all columns), every row becomes its own group and COUNT(*) is always 1. The grouping adds nothing. Ask yourself: "Am I actually summarizing anything?"

Practical Exercises

Exercise 1

Count the number of customers in each city (exclude customers with unknown cities).

SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC;

Expected output:

Exercise 2

Find the total revenue and average order value for each order status.

SELECT status,
       SUM(total_amount) AS total_revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

Expected output:

Exercise 3

Find the cheapest and most expensive product in each category, along with the category name.

SELECT c.name AS category,
       MIN(p.price) AS cheapest,
       MAX(p.price) AS most_expensive,
       MAX(p.price) - MIN(p.price) AS price_range
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
ORDER BY price_range DESC;

Expected output:

Exercise 4

Count how many orders each customer has placed, showing the customer name. Include customers with no orders.

SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC, customer;

Expected output:

Exercise 5

Find the total quantity of items ordered for each product, and show only products that have been ordered.

SELECT p.name AS product,
       SUM(oi.quantity) AS total_ordered,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY total_revenue DESC;

Expected output:

Key Takeaways

GROUP BY transforms aggregate functions from whole-table summaries into per-group summaries, enabling the analytical queries that power reports, dashboards, and business intelligence. Here is what you should remember:

• GROUP BY divides rows into groups based on one or more columns, then applies aggregate functions to each group separately
• Each unique value (or combination of values) in the GROUP BY columns creates one group and produces one output row
• Every column in your SELECT list must either appear in GROUP BY or be inside an aggregate function. No exceptions in standard SQL.
• GROUP BY with multiple columns creates groups based on each unique combination of values
• WHERE filters rows before grouping. Use it to exclude individual rows
• HAVING (next guide) filters groups after aggregation. Use it to exclude groups based on aggregate results
• The processing order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• NULL values form their own group in GROUP BY
• Combine GROUP BY with JOIN to display meaningful names instead of raw IDs
• Combine with ORDER BY to rank groups by their aggregate values
• Combine with LIMIT to get "top N" groups
• MySQL may silently allow non-aggregated columns without GROUP BY, returning arbitrary values. Enable ONLY_FULL_GROUP_BY mode to prevent this

GROUP BY is where SQL transitions from simple data retrieval into genuine data analysis. Combined with aggregate functions, it lets you answer complex business questions in a single query. The next guide covers HAVING, which adds the ability to filter groups after aggregation, completing your analytical toolkit.