Unable to understand why the WHERE clause is accepted.

Question

I am trying to understand the difference between HAVING and WHERE. I understand that HAVING 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 there be a valid select SUM(child_id) from children GROUP BY Child_ID HAVING child_ID = 5 statement select SUM(child_id) from children GROUP BY Child_ID HAVING child_ID = 5 ?

Answer 1

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

Answer 2

The WHERE clause can be used even if HAVING is used. These are different things. The way to think about it is as follows:

• WHERE clause acts as a filter at the record level
• Everything that passes is then placed in the groups indicated by your GROUP BY group
• Then the HAVING clause filters out groups based on the aggregate (SUM, COUNT, MIN, etc.)

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 

At first it will be filtered to read only entries in the Store with more than 100. For each group (by state) it will summarize the sales of only those stores where 100 or more are sold. Then it will cease any state if the summation at the state level does not exceed 1000. [Note: summation in the state excludes any sales volume of 100 or less.]