25

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!

Chris Craig
  • 7
  • 3
Stumbler
  • 2,056
  • 7
  • 35
  • 61
  • can you give sample records? Your problem is simple but you are statement is confusing. – John Woo Apr 02 '13 at 15:19
  • 2
    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? – Cyril Gandon Apr 02 '13 at 15:20
  • 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. – Stumbler Apr 02 '13 at 15:21

3 Answers3

32

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
jurgenreza
  • 5,856
  • 2
  • 25
  • 37
4

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
Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
0

You can do it as a two-step process by selecting the sum into a temp table, then selecting from the temp 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
techturtle
  • 2,519
  • 5
  • 28
  • 54
  • 2
    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. – jurgenreza Apr 02 '13 at 15:35
  • 1
    @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](http://meta.stackexchange.com/questions/66377/what-is-the-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. – techturtle Apr 02 '13 at 15:52