WHERE and HAVING issue 1

[Andrzejek]

I am a little confused about WHERE and HAVING when it comes to SQLs like these below.

Most of the time when I create any SQL with aggregated column, like SUM, I start with:[pre]
SELECT FIELD_A, FIELD_B
FROM MY_TABLE
WHERE (FIELD_A = ...)
ORDER BY FIELD_A[/pre]

Then I ‘wrap’ my FIELD_B in a SUM and GROUP BY whatever column(s) I need, and I have this SQL:[pre]
SELECT FIELD_A, SUM(FIELD_B) AS MYSUM
FROM MY_TABLE[blue]
WHERE (FIELD_A = ...)[/blue]
GROUP BY FIELD_A
ORDER BY FIELD_A[/pre]

Or I can run this SQL:[pre]
SELECT FIELD_A, SUM(FIELD_B) AS MYSUM
FROM MY_TABLE
GROUP BY FIELD_A[blue]
HAVING (FIELD_A = ...)[/blue]
ORDER BY FIELD_A[/pre]

And the outcome is the same from both SQLs.
Is there any ‘preference’ of which one to use? Is last one more ‘correct’ than the other?
In examples I see mostly the last one as the way to go, but the first one gives me the same outcome, so in my opinion is also correct. Right?

I would appreciate any light on the issue here.



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[fredericofonseca]

that's more of a misuse than anything else.
using the filter on the WHERE or JOIN clause is more correct from a code point of view only on your particular example, but in other cases it will give the wrong result

HAVING is targeted at filtering the "group by" results in a way that can not be directly done on a WHERE/JOIN clause
e.g.

select field_a
     , sum(field_b) as sum_result
from my_tbl
group by field_a
having sum(field_b) between 100 and 10000
or sum(field_b) < 20

to do the above without the HAVING you would need to code

select *
from (select field_a
          , sum(field_b) as sum_result
      from my_tbl
      group by field_a) t
where sum_result between 100 and 10000
or sum_result < 20

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

WHERE is applied when each row is selected from the table

HAVING is applied on the resulting rows of the query



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

I see. So in my example it does not matter because I used WHERE and HAVING on (non-aggregated) FIELD_A, but if I want to put criteria on FIELD_B I should be using HAVING syntax and avoid a long 'Select * from (Select...' statement Frederico shows.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[cmmrfrds]

Andy, I would not conclude that it doesn't matter. If you are dealing with small volume of rows in the tables, then it probably doesn't matter much, but it is always best to reduce the number of rows you select as early as possible and as discrete as possible. In your example, if the table contains 1 billion rows, then the Where Clause is the only way to go. Also, sometimes it is okay to add a few lines of code to make the query more efficient. I work in a data warehouse environment where hundreds of complex queries run each day and it is important to make the queries efficient or the processing bottlenecks. So, how you approach the query depends on the environment as well.