You can use a where clause in a statement with group by.
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.
The way SAP ASE 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. SAP ASE 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.