SQL: How to filter after aggregation?
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
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