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!

Solution 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

Solution 2

If you want to filter the sale with value less than 1000, the right query is

SELECT department, sales
FROM order_details
WHERE sales > 1000

If you want to aggregate, and keep only the sum be more than 15000, then you need this query :

SELECT department, SUM(sales) as TotalSales
FROM order_details
WHERE sales > 1000
GROUP BY department
HAVING SUM(sales) > 15000

Comments

[John Woo]

can you give sample records? Your problem is simple but you are statement is confusing.

[Cyril Gandon]

Not understanding your question. You are already doing what you ask for. You query filter all the departement with an aggregation less than 1000. Can you rephrase?

[Stumbler]

To rephrase: a filtering of the returned values. The query will exclude results less than X. Probably more efficient ways to go about it as it seems a bit of a waste to do calculation that will subsequently be excluded.

[jurgenreza]

This is exactly an example of what should not be done. Replace 1000 with 15000 in the first query and you get the same result.

[techturtle]

@jurgenreza You are right, of course, that it is a redundant filter. I was answering his question using his queries though (a bit of an XY problem). Still, based on his description, he could intend to filter by different fields in his actual query, and this approach would work for that.