question

phillipSmith avatar image
phillipSmith 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
wherefilterexpression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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;
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
phillipSmith avatar image
phillipSmith answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.