x

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

more ▼

asked Oct 11, 2016 at 10:02 AM in Default

avatar image

phillipSmith
1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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;
more ▼

answered Oct 11, 2016 at 10:50 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

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.

Oct 11, 2016 at 11:29 AM phillipSmith

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.

Oct 11, 2016 at 11:59 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 11, 2016 at 12:50 PM

avatar image

phillipSmith
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x24
x22
x11

asked: Oct 11, 2016 at 10:02 AM

Seen: 51 times

Last Updated: Oct 11, 2016 at 12:50 PM

Copyright 2017 Redgate Software. Privacy Policy