SQL standards state that the group by clause must contain items from the select list. However, Transact-SQL allows you to specify any valid column name in either the group by or select list, whether they employ aggregates or not.
Through the following extensions, Sybase lifts restrictions on what you can include or omit in the select list of a query that includes group by.
The columns in the select list are not limited to the grouping columns and columns used with the vector aggregates.
The columns specified by group by are not limited to those non-aggregate columns in the select list.
A vector aggregate must be accompanied by a group by clause. The SQL standards require that the non-aggregate columns in the select list match the group by columns. However, the first bulleted item described above allows you to specify additional, extended columns in the select list of the query.
For example, many versions of SQL do not allow the inclusion of the extended title_id column in the select list, but it is legal in Transact-SQL:
select type, title_id, avg(price), avg(advance)from titlesgroup by typetype title_id------------ -------- ----- -------business BU1032 13.73 6,281.25business BU1111 13.73 6,281.25business BU2075 13.73 6,281.25business BU7832 13.73 6,281.25mod_cook MC2222 11.49 7,500.00
mod_cook MC3021 11.49 7,500.00UNDECIDED MC3026 NULL NULLpopular_comp PC1035 21.48 7,500.00popular_comp PC8888 21.48 7,500.00popular_comp PC9999 21.48 7,500.00psychology PS1372 13.50 4,255.00psychology PS2091 13.50 4,255.00psychology PS2106 13.50 4,255.00psychology PS3333 13.50 4,255.00psychology PS7777 13.50 4,255.00trad_cook TC3218 15.96 6,333.33trad_cook TC4203 15.96 6,333.33trad_cook TC7777 15.96 6,333.33(18 rows affected)
The above example still aggregates the price and advance columns based on the type column, but its results also display the title_id for the books included in each group.
The second extension described above allows you to group columns that are not specified as columns in the select list of the query. These columns do not appear in the results, but the vector aggregates still compute their summary values. For example:
select state, count(au_id)from authorsgroup by state, citystate----- --------CA 2CA 1CA 5CA 5CA 2CA 1CA 1CA 1CA 1IN 1KS 1MD 1MI 1OR 1TN 1UT 2
(16 rows affected)
This example groups the vector aggregate results by both state and city, even though it does not display which city belongs to each group. Therefore, results are potentially misleading.
You may think the following query should produce similar results to the previous query, since only the vector aggregate seems to tally the number of each city for each row:
select state, count(au_id)from authorsgroup by city
However, its results are much different. By not using group by with both the state and city columns, the query tallies the number of each city, but it displays the tally for each row of that city in authors rather than grouping them into one result row per city.
state----- -----------CA 1CA 5CA 2CA 1CA 5KS 1CA 2CA 2CA 1CA 1TN 1OR 1CA 1MI 1IN 1CA 5CA 5CA 5MD 1CA 2CA 1UT 2UT 2(23 rows affected)
When you use the Transact-SQL extensions in complex queries that include the where clause or joins, the results may become even more difficult to understand. To avoid confusing or misleading results with group by, Sybase suggests that you use the fipsflagger option to identify queries that contain Transact-SQL extensions. See“group by and SQL standards” for details.
For more information about Transact-SQL extensions to group by and how they work, see the Reference Manual.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
