Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

GROUP BY with WHERE

You've seen that when there's no grouping and aggregates are working on the table as a whole, you can use the WHERE clause to specify which rows participate in the aggregate calculations. The same is true when you have groups. Here are the steps when a query includes WHERE, GROUP BY, and aggregates:

1.	The WHERE clause acts first to find the rows you want.
2.	The GROUP BY clause divides these favored few (or many) rows into groups.
3.	After the groups are formed, SQL calculates the group values.

Here's an example, first with and then without a limiting WHERE clause:

SQL

select type, avg(price)
from titles
where advance > 5000 group by type type avg(titles.price) ============ ================= popular_comp 41.48 trad_cook 35.47 ...