4

I am trying to understand the difference between HAVING and WHERE. I understand that HAVING is used with GROUP BY statements. However, I cannot understand why the following statement is accepted:

select SUM(child_id) from children WHERE child_ID = 5 GROUP BY Child_ID

Shouldn't the correct statement be select SUM(child_id) from children GROUP BY Child_ID HAVING child_ID = 5 ?

Dot NET
  • 4,891
  • 13
  • 55
  • 98
  • Either are valid, but the WHERE clause, for me, is actually the more standard usage. – Jason Whitish Mar 12 '13 at 20:34
  • @JasonWhisman - So you're saying I don't even need to use the HAVING clause at all and can accomplish all statements with the WHERE? – Dot NET Mar 12 '13 at 20:35
  • Right, everything but aggregate clauses. So you'd still have to do `HAVING SUM(child_id) > 0` for instance, but otherwise you're fine. – Jason Whitish Mar 12 '13 at 20:37

2 Answers2

6

WHERE clauses are executed before the grouping process has occurred, and only have access to fields in the input table. HAVING is performed after the grouping pocess occurs, and can filter results based on the value of aggregate values computed in the grouping process.

Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
  • So to see if I've understood, the WHERE conditions basically apply to filtering normal fields, while the HAVING clause would be applied if you want to filter the aggregate which you've selected? – Dot NET Mar 12 '13 at 20:49
2

The WHERE clause can be used even if a HAVING is being used. They mean very different things. The way to think about it is as follows:

  • The WHERE clause acts as a filter at the record level
  • Anything that gets through is then put into groups specified by your GROUP BY
  • Then, the HAVING clause filters out groups, based on aggregate (SUM, COUNT, MIN, etc.) condition

So, if I have a table : ( STORE_ID, STATE_CODE, SALES)

Select STATE, SUM(SALES)
from MyTable
Where SALES > 100
Group By STATE
Having Sum(Sales) > 1000

This will first filter to read only the Store records with Sales over 100. For each Group (by State) it will sum the Sales of only those stores with Sales of 100 or more. Then, it will drop any State unless the State-level summation is more than 1000. [Note: The state summation excludes any store of sales 100 or less.]

Darius X.
  • 2,886
  • 4
  • 24
  • 51
  • @DotNET: Exactly. There is also a case when you can use a HAVING without a GROUP BY. If you are aggregating everything into a single output row, you can still filter to either get that row or get nothing, thus: select SUM(Sales) from MyTable Having Sum(Sales)>50000 – Darius X. Mar 12 '13 at 20:57