By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

HAVING and WHERE explenation

100+
P: 374
I have built a sql string within access query design tool as it has been quite an effort to try and write this off the bat.

I have transfered this string into my code as DoCmd.RunSql. What I have done is used a combo box to make sure specific records are selected. Whilst doing this in the query designer I noticed it used the HAVING clause in order to select the right criteria.

I havent really come across it much since I'm pretty new to queries but to me surely HAVING and WHERE do pretty much the same job?

When and where should I be using each and what limitations do they have. I will hopefully be looking further into Allen Brownes form filtering script where he dynamically builds the string for filtering to possibly use this to create my sql string.

Thanks,

Chris

Ps. The string I've been talking about (looks so messy when using the query tool)
Expand|Select|Wrap|Line Numbers
  1. sqlstring = "SELECT tbldept.Department, tbltypes.NCType, Sum(tblCosts.CostFig) AS SumOfCostFig INTO tblParetoRaising FROM (tbldept INNER JOIN (tbltypes INNER JOIN tbllog ON tbltypes.NCtypeID = tbllog.NCType) ON tbldept.DeptID = tbllog.DeptRaisedBy) INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID GROUP BY tbldept.Department, tbltypes.NCType HAVING (((tbldept.Department)=[Forms]![frmParetoRaising]![cmoDeptRaisedBy]))ORDER BY Sum(tblCosts.CostFig) DESC "
  2. DoCmd.RunSQL sqlstring
Jul 21 '10 #1

✓ answered by patjones

They are similar, but not the same and not interchangeable. The WHERE clause is used prior to the GROUP BY being performed. The HAVING clause is used on the set of records that results after the GROUP BY.

So, this works:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...}
  2. FROM {...}
  3. WHERE {condition1}
  4. GROUP BY {...}
  5. HAVING {condition2};

But this does not:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...}
  2. FROM {...}
  3. WHERE {condition1}
  4. GROUP BY {...}
  5. WHERE {condition2};

Pat

Share this Question
Share on Google+
12 Replies


patjones
Expert 100+
P: 931
They are similar, but not the same and not interchangeable. The WHERE clause is used prior to the GROUP BY being performed. The HAVING clause is used on the set of records that results after the GROUP BY.

So, this works:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...}
  2. FROM {...}
  3. WHERE {condition1}
  4. GROUP BY {...}
  5. HAVING {condition2};

But this does not:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...}
  2. FROM {...}
  3. WHERE {condition1}
  4. GROUP BY {...}
  5. WHERE {condition2};

Pat
Jul 22 '10 #2

Delerna
Expert 100+
P: 1,134
Pretty much a repeat of what Zepphead 80 said

but the essential point to understand is
the where conditions are applied to each record prior to the result being grouped.
Having is applied to the recordset after the grouping has been applied.

step 1 the records are selected according to the where conditions
step 2 the selected records are grouped according to the group conditions
step 3 the grouped records are then selected according to the having conditions
step 4 the resulting recordset is returned
Jul 22 '10 #3

100+
P: 374
Thank you both for the replies. I have created something I'm now quite proud of once I got my understanding of HAVING and WHERE. I now have code which selects value from my main table based on form selections, creates a new table which then has values taken from that table and used within the record source of a pareto analysis graph using more select statements. A where clause has been built to allow data to be filtered within the first query using Allen Brownes technique of creating a dynamic where string.
Jul 22 '10 #4

NeoPa
Expert Mod 15k+
P: 31,768
Another point to bear in mind is that when you are in Design View of a query in Access and select the type of the query as GROUP BY (Click the button that looks like a Sigma), any existing fields that have criteria will be changed to HAVING, instead of the more sensible default of WHERE. It's worth looking out for this, understanding exactly what you really want, and changing to WHERE where appropriate.
Jul 22 '10 #5

Delerna
Expert 100+
P: 1,134
Thats the problem with developing with wizards...they don't always make the best choices.
Jul 23 '10 #6

100+
P: 374
@Delerna
I think that is the major pitfall I have found Delerna. I am alright at producing very very basic sql statements but just for ease I end up going in to the wizard but it doesn't take any time at all for the statements to look a real mess.
Jul 23 '10 #7

Delerna
Expert 100+
P: 1,134
Just to clarify my comment, I'm not against using wizards.
One good thing about them is that they provide a great tool to learn SQL from.

If youre not sure how to write a query to do a particular task then the wizard can provide a good place to start from and even many times a good place to end at, ie the code it produces is perfectly adequate.

Knowing they don't always make the best choices however means we can then analyse the resulting query for any improvements that can be made manually.

Obviously your ability to do that will improve as your experience grows.
Jul 25 '10 #8

NeoPa
Expert Mod 15k+
P: 31,768
That's exactly how I got started Chris. Looking at the SQL produced by the wizards.
Jul 26 '10 #9

Delerna
Expert 100+
P: 1,134
I became intereseted to see if there was a performance difference between the two so I wrote two queries that generate the same result one using where and having and one just using having
Expand|Select|Wrap|Line Numbers
  1. select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv 
  2. from Sales_Statistics
  3. Where company=100 and Division='NSW' and Facility='FN1'
  4. group by [Item Number],company,Division,Facility
  5. having sum([Invoiced Quantity])>2
  6.  
  7.  
  8. select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv 
  9. from Sales_Statistics
  10. group by [Item Number],company,Division,Facility
  11. having company=100 and Division='NSW' and Facility='FN1' and sum([Invoiced Quantity])>2
  12.  
I did this on MS SQL Server and the table has 3,144,135 records in it.

I then checked the execution plan for the two queries and they were exactly Identical (See attached) and when compared together query analyser reports that they will each take 50% of the time to execute them both.

So it seems there is no difference to the performance.....in SQL server anyway!
Attached Images
File Type: jpg QryAnalyser.jpg (6.2 KB, 201 views)
Jul 27 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
SQL Server is pretty clever Delerna. It is well within its capabilities to analyse the SQL strings and create identical execution plans for both.

This may not be true for all SQL servers (it would be for most mind you). That said, I find it is more about human understanding than about execution. When people better understand what the code means, they tend to make fewer errors.
Jul 27 '10 #11

Delerna
Expert 100+
P: 1,134
Yes I agree.
Its why I added the bolded
So it seems there is no difference to the performance.....in SQL server anyway!
Jul 28 '10 #12

NeoPa
Expert Mod 15k+
P: 31,768
I didn't miss that :)

The point of my post was to draw attention away from performance as the defining issue here. I wasn't trying to argue with you on the point you made (in fact I supported it essentially).
Jul 28 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.