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