Two customers spend ₹1,000 each. The sales dashboard counts both orders correctly and reports ₹4,000 in revenue.
One basket holds a notebook, pens and a ₹150 mug. When the sales manager selects Stationery, should the mug count?
The dashboard joins orders to line items to make that filter possible. Even with All products selected, the query adds each order’s full value once per matching line.
How one ₹1,000 order becomes ₹3,000 in a SQL join
Take two paid orders. In this example, revenue is the amount paid for the items, and the line amounts add up to each order total. Order 101 is the three-item basket. Order 102 contains a single desk lamp.
Here’s what the data looks like after joining orders to order_items on order_id.
| Order ID | Item | Category | Line amount | Order total |
|---|---|---|---|---|
| 101 | Notebook | Stationery | ₹600 | ₹1,000 |
| 101 | Pens | Stationery | ₹250 | ₹1,000 |
| 101 | Mug | Homeware | ₹150 | ₹1,000 |
| 102 | Desk lamp | Homeware | ₹1,000 | ₹1,000 |
Read across the mug row. The ₹150 is the price of the mug. The ₹1,000 is the price of the whole basket. Now read down the last column. Order 101 contributes ₹3,000 because its basket total appears beside each of its three items. The lamp order contributes ₹1,000.
SELECT COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.order_total) AS reported_revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;
The query returns 2 orders and ₹4,000. This is a one-to-many join, and it changes the grain, or what one row represents. Before the join, one row represents an order. After the join, one row represents an order line. Both orders have matching items, so the count still finds two distinct order IDs. The sum adds the basket total on every row. PostgreSQL documentation on joined tables
Why SUM(DISTINCT order_total) can undercount revenue
Try SUM(DISTINCT o.order_total) on order 101 alone and it returns ₹1,000. That looks like a fix. Include order 102 and the answer is still ₹1,000. Both customers spent the same amount, so SQL sees just one distinct numeric value. Microsoft’s reference for SUM and DISTINCT
Those two equally priced orders make a good test case. One needs several lines to expose the inflated sum. The second needs the same order total to expose the undercount. A test that checks only the order count would pass in both cases.
What should revenue mean when someone filters by product?
Start with a number you can reconcile. With all products selected, the two paid orders total ₹2,000. The orders table already has one row per order.
SELECT SUM(order_total) AS order_revenue
FROM orders;
Back to the ₹150 mug. The sales manager’s choice depends on which of these two figures the dashboard is meant to report.
How much did we sell in each category? Add the line amounts. The notebook and pens contribute ₹850 to Stationery. The mug and desk lamp contribute ₹1,150 to Homeware. The two categories add up to the ₹2,000 customers paid.
SELECT category, SUM(line_amount) AS item_sales
FROM order_items
GROUP BY category
ORDER BY category;
If your real orders include a basket-wide discount, decide how to allocate it across items before reconciling category sales to the amount paid.
How much did customers spend on orders that included stationery? Use the full value of each qualifying basket, mug included. The answer is ₹1,000. Use EXISTS to check each order for at least one stationery line. The notebook and pens both match, and the outer query still sums order 101 just once. PostgreSQL documentation on EXISTS
SELECT SUM(o.order_total) AS order_value_with_stationery
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM order_items AS i
WHERE i.order_id = o.order_id
AND i.category = 'Stationery'
);
That distinction matters when someone adds a total row to the dashboard. Orders containing stationery are worth ₹1,000. Orders containing homeware are worth ₹2,000. Adding those figures gives ₹3,000 because the first basket belongs to both groups. The grand total across all orders is still ₹2,000.
Before you sign off on the dashboard, try one small change. Split the ₹250 pens line into two ₹125 lines on the same order. The customer paid the same amount, so total sales across both orders should still be ₹2,000. If the dashboard jumps from ₹4,000 to ₹5,000, that extra line just made it count the entire ₹1,000 basket again.
Then give the product filter a name that tells the reader what it measures. Stationery sales comes to ₹850. Value of orders containing stationery comes to ₹1,000. The difference is the mug. Once the label makes that clear, the sales manager can read either number without having to read the SQL.
Appendix: Run the example in SQLite
In a fresh SQLite database, run this setup once, then run the four queries above. All amounts are in whole rupees.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
order_total INTEGER NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(order_id),
line_id INTEGER NOT NULL,
item TEXT NOT NULL,
category TEXT NOT NULL,
line_amount INTEGER NOT NULL,
PRIMARY KEY (order_id, line_id)
);
INSERT INTO orders (order_id, order_total) VALUES
(101, 1000),
(102, 1000);
INSERT INTO order_items (order_id, line_id, item, category, line_amount) VALUES
(101, 1, 'Notebook', 'Stationery', 600),
(101, 2, 'Pens', 'Stationery', 250),
(101, 3, 'Mug', 'Homeware', 150),
(102, 1, 'Desk lamp', 'Homeware', 1000);


Top comments (0)