Now ask a more report-like question: “How much revenue did each customer generate?”
First, define revenue per row as quantity * unit_price. Then aggregate by customer:
SELECT customer, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer;
Worked result:
| customer | revenue |
|---|---|
| Acorn Co | 195.00 |
| Bright Ltd | 61.00 |
| Delta Inc | 170.00 |
How those totals were built:
5*12.00 + 20*2.50 + 1*85.00 = 60 + 50 + 85 = 19510*2.50 + 3*12.00 = 25 + 36 = 612*85.00 = 170GROUP BY customer changes the shape of the result. Instead of one row per order, you now get one row per customer.
Suppose the question changes to: “For East-region orders only, what revenue did each customer generate?” That means filter rows first, then summarize:
SELECT customer, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE region = 'East'
GROUP BY customer;
Result:
| customer | revenue |
|---|---|
| Acorn Co | 195.00 |
Now a different question: “Which customers have total revenue above 100?” That is not a row filter. You first build customer totals, then keep only the groups above 100:
SELECT customer, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer
HAVING SUM(quantity * unit_price) > 100;
Result:
| customer | revenue |
|---|---|
| Acorn Co | 195.00 |
| Delta Inc | 170.00 |
WHEREfilters raw rows.HAVINGfilters aggregated groups.
Use HAVING because the condition is about a group-level total, not an individual row. WHERE runs before grouping; HAVING runs after the grouped totals exist.
Why does this query return only Acorn Co?
SELECT customer, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE region = 'East'
GROUP BY customer;
This is the key order of operations idea: WHERE filters rows first, so the query only sees East-region orders before it groups anything. In this tiny table, all East rows belong to Acorn Co, so only that customer remains to be grouped. The idea that GROUP BY removes single-order customers is a common misunderstanding; it groups whatever rows survive the filter, even if there is only one row. The claim that SUM can only handle one customer at a time mistakes the purpose of grouping — SUM works per group just fine. The suggestion that region must be selected confuses display with logic; a column can be used in WHERE without appearing in the result.