GROUP BY and the WHERE Clause
You can use a WHERE clause in a query containing a GROUP BY clause. Rows not meeting the conditions in the WHERE clause are eliminated before any grouping is done. For example:
USE AdventureWorks;
GO
SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID ;
GO
Here is the result set.
ProductModelID Average List Price
-------------- ------------------------------
5 1357.0500
6 1431.5000
7 1003.9100
19 3378.4382
20 2297.2627
21 1079.9900
25 3578.2700
26 2443.3500
27 1700.9900
28 1457.9900
29 1120.4900
34 2384.0700
35 1214.8500
39 3374.9900
40 1120.4900
41 1700.9900
(16 row(s) affected)
Only rows with list prices greater than $1,000 are included in the groups shown in the query results.
See Also
Other Resources
SELECT (Transact-SQL)
WHERE (Transact-SQL)