A sales report can return plausible totals while answering the wrong question. One common cause is filtering individual transactions when the requirement was to filter the combined result for each customer.
Oracle SQL provides both WHERE and HAVING, but they operate at different levels of a grouped query. Understanding that difference starts with describing which rows should contribute and which completed groups should appear.
Use WHERE to define the contributing rows
A WHERE condition restricts the rows selected by a query. In a typical grouped report, this establishes the population from which the aggregates are calculated.
Suppose a report should consider only completed orders. The row-level condition belongs in WHERE, because an order’s status determines whether it contributes to the report at all.
This is a question about individual records: should this particular order be included?
Oracle’s SELECT documentation distinguishes this filtering from the restrictions applied to grouped results.
Use HAVING to select completed groups
Aggregate functions summarize groups of rows. HAVING can then restrict the output according to those aggregate results. Oracle explicitly documents its use for eliminating groups based on aggregate values rather than individual row values.
For an illustrative orders table, the query might be:
SELECT customer_id,
SUM(order_total) AS completed_total
FROM orders
WHERE status = 'COMPLETE'
GROUP BY customer_id
HAVING SUM(order_total) >= 1000;This asks for customers whose completed orders total at least 1,000. It does not require every contributing order to be worth 1,000.
The distinction becomes clear with a small example. A customer with two completed orders of 600 contributes a total of 1,200 and qualifies. A customer with a single completed order of 900 does not.
Watch for a valid query that changes the question
Now consider placing order_total >= 1000 in the WHERE clause. That removes the two 600 orders before the customer total is calculated.
The resulting query may run without an error. Its meaning has changed to something closer to “summarize qualifying large orders,” rather than “find customers with qualifying combined spending.”
This is why SQL review should include representative data, not only syntax checks. A query can be technically valid while implementing the wrong business rule.
Write the requirement in two sentences before editing SQL: “Include these records in the calculation” and “Show groups meeting this condition.” The sentences often reveal where each predicate belongs.
Check boundary cases explicitly
Build a small review dataset with one customer just below the threshold, one exactly on it, and one above it. Include a customer whose qualifying total is made from several smaller orders.
Also include an excluded status with a large amount. That row should not affect a report defined around completed orders.
If refunds, cancellations, or missing amounts are possible, establish how they should affect the calculation before finalizing the query. Their business treatment cannot be inferred from the presence of SUM.
Finally, describe the result precisely in the report heading. “Customers with completed-order totals of at least 1,000” communicates more than “High-value customers.”
Correct placement of WHERE and HAVING keeps the selected population and the displayed summary aligned with the question the report is supposed to answer.