Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A simple SQL question

 
Cameron Park
Ranch Hand
Posts: 371
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I have a very uneducated question regarding SQL. What is the difference between HAVING and WHERE?
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Cameron Park:
Hi, I have a very uneducated question regarding SQL. What is the difference between HAVING and WHERE?

The WHERE clause is applied [/I]before[/I] grouping occurs, the HAVING clause after.
Consider
SELECT dept, count(*)
FROM emp
WHERE salary > 100000
GROUP BY dept
This will take the employees whose salary exceeds 100000, and count the number of such employees grouped on a per department basis. Note that the salary condition is applied before any grouping occurs. Contrast this with
SELECT dept, count(*)
FROM emp
GROUP BY dept
HAVING AVG(salary) > 100000
This will first count employees, grouped per department, and from this list show you only those departments where the average salary exceeds 100000. The condition is applied to the groups themselves, not to the records used in constructing the groups.
- Peter
 
Cameron Park
Ranch Hand
Posts: 371
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Peter.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic