SQL GROUP BY

GROUP BY splits rows into groups that share a value and runs an aggregate on each group. Instead of one total for the whole table, you get one total per status, per product, per city or per month.

Syntax

SELECT group_column, AGGREGATE(other_column)
FROM table_name
WHERE row_condition
GROUP BY group_column
ORDER BY ...;

Examples

1. Count per group

How many orders are in each status?

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

Result:

(5 rows)

Think of it as sorting the 24 orders into five piles by status, then counting each pile.

2. Sum per group

Units sold and net revenue per product:

SELECT product_id,
       SUM(quantity) AS units_sold,
       ROUND(SUM(quantity * unit_price * (1 - discount_pct / 100)), 2) AS net_revenue
FROM order_items
GROUP BY product_id
ORDER BY net_revenue DESC
LIMIT 5;

Result:

(5 rows)

You'll replace product_id with the product name once you learn joins.

3. Group by multiple columns

Orders by payment method and status:

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

Result:

(12 rows)

Each unique combination becomes its own group.

4. Group by an expression

Monthly order count, using DATE_TRUNC from the date functions lesson:

SELECT DATE_TRUNC('month', order_date)::date AS month,
       COUNT(*) AS orders
FROM orders
WHERE order_date >= '2025-07-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Result:

(8 rows)

5. Several aggregates per group

A quick profile of each category's catalogue:

SELECT category_id,
       COUNT(*) AS products,
       MIN(price) AS min_price,
       MAX(price) AS max_price,
       SUM(stock_qty) AS total_stock
FROM products
GROUP BY category_id
ORDER BY category_id;

Result:

(6 rows)

6. WHERE before GROUP BY

Only delivered orders, grouped by customer:

SELECT customer_id,
       COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'Delivered'
GROUP BY customer_id
ORDER BY delivered_orders DESC, customer_id
LIMIT 5;

Result:

(5 rows)

WHERE removes rows first; then the remaining rows are grouped.

Common mistakes

Selecting a column that isn't grouped or aggregated:

SELECT status, payment_method, COUNT(*)
FROM orders
GROUP BY status;

Error: column "orders.payment_method" must appear in the GROUP BY clause or be used in an aggregate function

Each status group contains several payment methods, so SQL can't pick one. Add payment_method to GROUP BY or aggregate it.

• Filtering groups with WHERE. WHERE COUNT(*) > 2 fails. Group filters go in HAVING.
• Grouping by a column with messy values. 'Bengaluru' and 'bengaluru ' become two groups. Clean with TRIM/INITCAP first.

Practice question

For each city, show how many customers ShopKart has and how many of them are Prime members. Sort by customer count, highest first.

SELECT city,
       COUNT(*) AS customers,
       COUNT(*) FILTER (WHERE is_prime) AS prime_customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city
LIMIT 5;

Key takeaways

• GROUP BY makes aggregates work per group instead of per table.
• Every selected column must be grouped or aggregated.
• You can group by several columns or by an expression like DATE_TRUNC.
• WHERE filters rows before grouping; HAVING filters groups after.