where clause and group by

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)

It only seems as if the query is ignoring the where clause when you look at the results for the advance (extended) column. 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, you must use a having clause (described later in this chapter).