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.
asked Oct 11, 2016 at 10:02 AM in Default
You can put all predicates in the HAVING clause.
Both these works, and they produce the same execution plan.
answered Oct 11, 2016 at 10:50 AM
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.
answered Oct 11, 2016 at 12:50 PM