SQL: How to filter after aggregation?

Question

It is very easy to remove values that you don't want aggregated.

For instance:

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Which will exclude all sales with a value less than or equal to 1000 from the summing aggregation.

But how do you filter after the aggregation?

E.g. WHERE ("Total sales"> 15000)

Edit: Ironically I was only including HAVING SUM(sales) > 1000; in order to prevent confusion about the type of query required; because I'm not actually interested in excluding items from the summing, just the returned results! Thanks, despite confusion!

Answer 1

The query you have is actually doing what you want and not what you expressed in the question. If you want to exclude all sales with a value less than 1000, you should use WHERE sales > 1000. But with HAVING SUM(sales) > 1000 the filtering is actually done after the aggregation.

Writing a sub-query and adding another SELECT WHERE on top of the original query is redundant.

Please see fiddle for clarification.

#Query1

SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING Total > 40;

#Query 2

SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING  SUM(sales) > 40;

#Query 3

SELECT department, SUM(sales) as Total
FROM order_details
WHERE sales > 40
GROUP BY department;

#Query 1 and 2 are the same, filtering after aggregation
#Query 3 is filtering before aggregation