SQL GROUP BY to summarize data

Aggregate rows into groups and calculate a summary for each group.

GROUP BY divides rows into groups and applies aggregate functions to each group separately.

Three order rows sorted into two groups by user_id, with the sum of each group shown in the result table — GROUP BY user_id puts rows with the same user_id in one group, then SUM(total) runs once per group.

Basic Syntax

SELECT 
    grouping_column,
    AGGREGATE_FUNCTION(column)
FROM table
GROUP BY grouping_column;

Examples

-- Count users by country
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- Total revenue per customer
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

-- Average rating per product
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id;

Multiple Group Columns

-- Sales by year and month
SELECT 
    strftime('%Y', created_at) AS year,
    strftime('%m', created_at) AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY 
    strftime('%Y', created_at),
    strftime('%m', created_at)
ORDER BY year, month;

-- Users by country and status
SELECT country, status, COUNT(*)
FROM users
GROUP BY country, status;

GROUP BY with JOIN

-- Top customers by total spending
SELECT 
    u.name,
    u.email,
    COUNT(o.id) AS orders,
    SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC;

Rule: Every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. You can't select user.name without grouping by it.

Try It Yourself

Use the orders table from the introduction to JOINs. The query counts and totals the orders of each user:

SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY user_id;

user_id  order_count  total_spent
-------  -----------  -----------
      1            2          225
      2            1          200