asked
Filtering using expressions that logically mix Having and Where Clauses
Hi Is it possible to filter data in a table with a Boolean expression that logically mixes expressions that in SQL are separated into the WHERE Clause and the HAVING clause. For example. How would I achieve the following: (Category = 'Drinks' AND SUM(Sales) > 1000) OR (Category = 'Vegatebles' AND SUM(Sales) > 500) I'm building an expression engine using a graphical drag and drop User Interface. The expressions could get lengthy so I'm looking for a way to programmatically construct the SQL syntax. Regards Phil
You can put all predicates in the HAVING clause. Both these works, and they produce the same execution plan. select year(orderdate),SUM(totaldue) from Sales.SalesOrderHeader group by YEAR(OrderDate) having SUM(totaldue)>100 and YEAR(OrderDate) between 2010 and 2012; select year(orderdate),SUM(totaldue) from Sales.SalesOrderHeader where YEAR(OrderDate) between 2010 and 2012 group by YEAR(OrderDate) having sum(totaldue)>100;
Oh, you absolute superstar. I didn't consider this. I owe you a beer. You have just saved me a whole load of work. Thank you so much Magnus.
Np. If you found the answer useful, please check the green checkbox and/or vote the answer, so that other users can see what was considered helpful.
Ah, slight gotcha. In the having clause each term must be in the group by clause which is not necessarily going to be the case. For example I might want an additional filter of County = 'Hampshire' but I do not want this as part of my grouping. I'm experimenting with having an outer and inner grouping but it wont work for AVG, StDev etc.
17 People are following this question.
