SQL Aggregations for Business Reports

By the end of this lesson you will be able to turn raw order rows into the summary tables that managers actually read: revenue per month, orders per status, sales by price band and each category's share of the total. These are the queries behind most weekly reports.

An aggregation squeezes many rows into one number: a total, a count, an average. Think of the evening cash count at a petrol pump. Hundreds of small payments during the day become a few lines in the register: total cash, total UPI, total card. GROUP BY is how SQL writes that register.

The five aggregate functions

On their own, these return a single row for the whole table. With GROUP BY, they return one row per group.

Step 1: revenue per month

Revenue lives in order_items (quantity times unit price), and the date lives in orders. We join the two, skip cancelled orders and group by month. SUBSTR(order_date, 1, 7) takes the first seven characters of the date, such as 2025-03, and works the same in SQLite and MySQL.

SELECT SUBSTR(o.order_date, 1, 7) AS month,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY SUBSTR(o.order_date, 1, 7)
ORDER BY month;

month   | orders | revenue
--------+--------+--------
2025-01 | 3      | 23544
2025-02 | 2      | 12947
2025-03 | 3      | 7697
2025-04 | 3      | 43691
2025-05 | 3      | 16595
(5 rows)

What you should see: five months with orders and revenue. Notice COUNT(DISTINCT o.order_id). After the join, an order with two items appears on two rows, so a plain COUNT(*) would count items, not orders. This is one of the most common reporting mistakes, so always ask yourself "what does one row mean after my join?"

Step 2: filter groups with HAVING

WHERE filters rows before grouping. HAVING filters groups after grouping. Business question: which customers have spent more than ₹10,000 in total?

SELECT c.first_name,
       c.city,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.city
HAVING SUM(oi.quantity * oi.unit_price) > 10000
ORDER BY total_spent DESC;

first_name | city   | total_spent
-----------+--------+------------
Sneha      | Delhi  | 32797
Priya      | Jaipur | 16899
Aarav      | Rohtak | 14944
Rohan      | Mumbai | 14397
(4 rows)

What you should see: a handful of high-value customers. You cannot write this condition in WHERE, because the total does not exist until the rows are grouped.

Step 3: put rows into buckets with CASE

CASE works like an IF in Excel: it checks conditions in order and returns the first matching value. Analysts use it to create bands (groups of ranges) that are easier to read than raw numbers.

SELECT CASE
         WHEN unit_price < 1000 THEN '1. Under 1,000'
         WHEN unit_price < 5000 THEN '2. 1,000 to 4,999'
         ELSE '3. 5,000 and above'
       END AS price_band,
       COUNT(*) AS items_sold,
       SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY price_band
ORDER BY price_band;

price_band         | items_sold | revenue
-------------------+------------+--------
1. Under 1,000     | 7          | 8191
2. 1,000 to 4,999  | 9          | 23789
3. 5,000 and above | 6          | 78993
(3 rows)

What you should see: three price bands. The cheapest band brings only about ₹8,000 from 7 items, while just 6 expensive items bring in about ₹79,000, most of the revenue. The numbers 1., 2., 3. at the start of each label make the bands sort in a sensible order.

Step 4: conditional totals in one row

Managers love one-line summaries. You can put several counts side by side by placing a CASE inside SUM. This is called conditional aggregation.

SELECT COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS open_orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS delivered_pct
FROM orders;

all_orders | delivered | open_orders | cancelled | delivered_pct
-----------+-----------+-------------+-----------+--------------
15         | 10        | 4           | 1         | 66.7
(1 row)

What you should see: one row with all the order counts and the delivered percentage. Note the * 100.0. Without the decimal point, SQLite would divide whole numbers and drop the fraction, as you saw in Tools & Practice Data.

Step 5: share of total

"What share of revenue does each category bring?" needs two numbers: the category total and the grand total. A subquery (a query inside another query) can supply the grand total.

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       ROUND(SUM(oi.quantity * oi.unit_price) * 100.0 /
             (SELECT SUM(quantity * unit_price) FROM order_items), 1) AS pct_of_total
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

category    | revenue | pct_of_total
------------+---------+-------------
Displays    | 47997   | 43.3
Audio       | 22996   | 20.7
Accessories | 21987   | 19.8
Storage     | 15743   | 14.2
Books       | 2250    | 2
(5 rows)

What you should see: the percentages add up to about 100. Displays alone bring a large share. In Window Functions for Business Questions you will learn a neater way to get the grand total with SUM(...) OVER ().

Common mistakes

• Counting rows instead of orders after a join. Use COUNT(DISTINCT order_id).
• Putting a group condition in WHERE. Conditions on totals or counts go in HAVING.
• Whole-number division. Multiply by 100.0 before dividing.
• Selecting a column that is not grouped. In SELECT city, SUM(...), every non-aggregated column must be in GROUP BY. MySQL rejects it by default; SQLite quietly picks any value.

Key points

• COUNT, SUM, AVG, MIN and MAX squeeze many rows into one number.
• GROUP BY gives one result row per group; HAVING filters those groups.
• After a join, check what one row means and use COUNT(DISTINCT ...) for orders.
• CASE creates readable bands; SUM(CASE ...) puts several counts in one row.
• Share of total = group total times 100.0 divided by the grand total.

SELECT city,
       COUNT(*) AS customers,
       SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS no_email
FROM customers
GROUP BY city
HAVING SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) >= 1
ORDER BY city;