HAVING and WHERE explenation

[munkee]

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)

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 "
DoCmd.RunSQL sqlstring

[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:

SELECT {...}
FROM {...}
WHERE {condition1}
GROUP BY {...}
HAVING {condition2};

But this does not:

SELECT {...}
FROM {...}
WHERE {condition1}
GROUP BY {...}
WHERE {condition2};

Pat

[Delerna]

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

[munkee]

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.

[NeoPa]

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.

[Delerna]

Thats the problem with developing with wizards...they don't always make the best choices.

[munkee]

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

[Delerna]

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.

[NeoPa]

That's exactly how I got started Chris. Looking at the SQL produced by the wizards.

[Delerna]

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

select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv 
from Sales_Statistics
Where company=100 and Division='NSW' and Facility='FN1'
group by [Item Number],company,Division,Facility
having sum([Invoiced Quantity])>2
 
 
select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv 
from Sales_Statistics
group by [Item Number],company,Division,Facility
having company=100 and Division='NSW' and Facility='FN1' and sum([Invoiced Quantity])>2
 

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!

[NeoPa]

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.

[Delerna]

Yes I agree.
Its why I added the bolded

So it seems there is no difference to the performance.....in SQL server anyway!

[NeoPa]

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