INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

WHERE and HAVING issue

WHERE and HAVING issue

(OP)
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:
  SELECT FIELD_A, FIELD_B
  FROM MY_TABLE
  WHERE (FIELD_A = ...)
  ORDER BY FIELD_A 

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

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

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.

RE: WHERE and HAVING issue

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.

CODE

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: WHERE and HAVING issue


WHERE is applied when each row is selected from the table

HAVING is applied on the resulting rows of the query
noevil

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

RE: WHERE and HAVING issue

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

RE: WHERE and HAVING issue

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close