You can use a where clause in a statement with group by. Rows that do not satisfy the conditions in the where clause are eliminated before any grouping is done. Here is an example:
select type, avg(price) from titles where advance > 5000 group by type
type ------------- -------- business 2.99 mod_cook 2.99 popular_comp 21.48 psychology 14.30 trad_cook 17.97 (5 rows affected)
Only the rows with advances of more than $5000 are included in the groups that are used to produce the query results.
However, the way that Adaptive Server handles extra columns in the select list and the where clause may seem contradictory. For example:
select type, advance, avg(price) from titles where advance > 5000 group by type
type advance ------------- --------- -------- business 5,000.00 2.99 business 5,000.00 2.99 business 10,125.00 2.99 business 5,000.00 2.99 mod_cook 0.00 2.99 mod_cook 15,000.00 2.99 popular_comp 7,000.00 21.48 popular_comp 8,000.00 21.48 popular_comp NULL 21.48 psychology 7,000.00 14.30 psychology 2,275.00 14.30 psychology 6,000.00 14.30 psychology 2,000.00 14.30 psychology 4,000.00 14.30 trad_cook 7,000.00 17.97 trad_cook 4,000.00 17.97 trad_cook 8,000.00 17.97 (17 rows affected)
When you look at the results for the advance (extended) column, it may seem as though the query is ignoring the where clause. Adaptive Server still computes the vector aggregate using only those rows that satisfy the where clause, but it also displays all rows for any extended columns that you include in the select list. To further restrict these rows from the results, use a having clause (described later in this chapter).
