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;
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.