When you include the having, group by, and where clauses in a query, the sequence in which each clause affects the rows determines the final results:
The where clause excludes rows that do not meet its search conditions.
The group by clause collects the remaining rows into one group for each unique value in the group by expression.
Aggregate functions specified in the select list calculate summary values for each group.
The having clause excludes rows from the final results that do not meet its search conditions.
The following query illustrates the use of where, group by, and having clauses in one select statement containing aggregates:
select stor_id, title_id, sum(qty) from salesdetail where title_id like "PS%" group by stor_id, title_id having sum(qty) > 200
stor_id title_id ------- -------- ----------- 5023 PS1372 375 5023 PS2091 1,845 5023 PS3333 3,437 5023 PS7777 2,206 6380 PS7777 500 7067 PS3333 345 7067 PS7777 250 (7 rows affected)
The query executed in this order:
The where clause identified only rows with title_id beginning with “PS” (psychology books),
group by collected the rows by common stor_id and title_id,
The sum aggregate calculated the total number of books sold for each group, and
The having clause excluded from the final results the groups for which the book totals do not exceed 200 books.
All of the previous having examples in this section adhere to the SQL standards, which specify that columns in a having expression must have a single value, and must be in the select list or group by clause. However, the Transact-SQL extensions to having allow columns or expressions not in the select list and not in the group by clause.
The following example determines the average price for each title type, but excludes those types that do not have more than $10,000 in total sales, even though the sum aggregate does not appear in the results.
select type, avg(price) from titles group by type having sum(total_sales) > 10000
type ------------ ---------- business 13.73 mod_cook 11.49 popular_comp 21.48 trad_cook 15.96 (4 rows affected)
The extension behaves as if the column or expression were part of the select list but not part of the results. If you include a nonaggregated column with having, but it is not part of the select list or the group by clause, the query produces results similar to the “extended” column extension described earlier in this chapter. For example:
select type, avg(price) from titles group by type having total_sales > 4000
type ------------ ---------- business 13.73 business 13.73 business 13.73 mod_cook 11.49 popular_comp 21.48 popular_comp 21.48 psychology 13.50 trad_cook 15.96 trad_cook 15.96 (9 rows affected)
Unlike an extended column, the total_sales column does not appear in the final results, yet the number of displayed rows for each type depends on the total_sales for each title. The query indicates that three business, one mod_cook, two popular_comp, one psychology, and two trad_cook titles exceed $4000 in total sales.
As mentioned earlier, the way Adaptive Server handles extended columns may seem as if the query is ignoring the where clause in the final results. To make the where conditions affect the results for the extended column, repeat the conditions in the having clause. For example:
select type, advance, avg(price) from titles where advance > 5000 group by type having advance > 5000
type advance ------------- --------- -------- business 10,125.00 2.99 mod_cook 15,000.00 2.99 popular_comp 7,000.00 21.48 popular_comp 8,000.00 21.48 psychology 7,000.00 14.30 psychology 6,000.00 14.30 trad_cook 7,000.00 17.97 trad_cook 8,000.00 17.97 (8 rows affected)
