Geek Answers Handbook

SQL: how to filter after aggregation?

It is very easy to remove values ​​that you do not want to aggregate.

For instance:

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

Which excludes all sales with a value less than or equal to 1000 from the summation.

But how do you filter after aggregation?

For example, WHERE ("Total sales"> 15000)

Edit: ironically, I only HAVING SUM(sales) > 1000; to avoid confusion regarding the type of request being requested; because I'm not really interested in excluding items from the summation, only the returned results! Thank you, despite the confusion!

+9
source share
3 answers

The request you have is actually doing what you want, 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 filtering is actually performed after aggregation.

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

See fiddle for more details.

 #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
+16
source

If you want to filter a sale with a value less than 1000, the correct query

 SELECT department, sales FROM order_details WHERE sales > 1000

If you want to copy and save only the amount over 15000, you need this request:

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

You can do this as a two-step process by selecting the amount in the temp table, then selecting temp from the table:

 SELECT department, SUM(sales) as TotalSales INTO #temp FROM order_details GROUP BY department HAVING SUM(sales) > 1000 SELECT department, TotalSales FROM #temp WHERE TotalSales > 15000 DROP TABLE #temp
0
source

More articles:


All Articles