Brightlane's finance team has a reporting rule: produce the total-revenue figure only if there are at least 50 orders to analyse — otherwise the dataset is too thin to be considered reliable.
Write a query to return the total order amount if and only if this threshold is met.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The threshold check applies to the entire order history treated as one group — the report is one number, not one number per anything.
- If the threshold is met, the output is one row containing the total. If the threshold is not met, the output is zero rows.
Output:
- A single row with one column,
total_revenue, when the threshold is met. Otherwise, no rows.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
SUM(total_amount) AS total_revenue
FROM
orders
HAVING
COUNT(*) >= 50The shape
No GROUP BY, but a HAVING. The query treats the entire orders table as one implicit group, computes SUM(total_amount) and COUNT(*) across that single group, and lets HAVING COUNT(*) >= 50 decide whether the single result row survives or disappears. Brightlane has well over 50 orders, so the row survives carrying total_revenue = 126725.73. If the table held only 40 orders, the same query would return zero rows.
Clause by clause
SELECT SUM(total_amount) AS total_revenueis the aggregate. With noGROUP BY,SUMcollapses the entireorderstable into one number — the platform-wide revenue total.FROM ordersis the source set: every order Brightlane has processed.HAVING COUNT(*) >= 50filters the implicit single group by its row count. The reliability rule is a condition on the whole dataset, not on any per-row property, so it belongs inHAVINGeven though noGROUP BYclause is present. IfCOUNT(*)is below50, the entire result row is suppressed.
Why this and not WHERE COUNT(*) >= 50
WHERE can't see COUNT(*) for the same reason it can't anywhere else — the aggregate doesn't exist until after the row set has been collapsed. The absence of a GROUP BY doesn't change that. The query still has a single implicit group; the count of that group still only exists after the rows have been aggregated.
A different-shaped attempt would be to run the query and then check the count in application code. That works, but it materialises a result row that may not be wanted. The HAVING form is server-side: the result row only appears if the reliability threshold is met. The reporting tool downstream sees zero rows when the data is too thin, which is exactly the contract finance asked for.
The trap
The trap is assuming HAVING requires GROUP BY. It doesn't. A query without GROUP BY is a query with exactly one group — the whole row set — and HAVING filters that group the same way it filters explicit groups. The result is binary: one row if the condition holds, zero rows if it doesn't.
The rule worth carrying: HAVING is a filter on aggregates. Whenever an aggregate exists (whether through GROUP BY or implicitly across the whole table), HAVING can filter on it. The GROUP BY is not what authorises HAVING; the aggregate is.
You practiced using HAVING without GROUP BY. The recurring rule: a query without GROUP BY is one implicit group — the whole row set — and HAVING decides whether the result has one row or zero.
