The keyword all in the group by clause is a Transact-SQL enhancement. It is meaningful only if the select statement in which it is used also includes a where clause.
If you use all, the query results include all the groups produced by the group by clause, even if some groups do not have any rows that meet the search conditions. Without all, a select statement that includes group by does not show groups for which no rows qualify.
Here is an example:
select type, avg(advance) from titles where advance > 1000 and advance < 10000 group by type
type ------------ ------------------------ business 5,000.00 popular_comp 7,500.00 psychology 4,255.00 trad_cook 6,333.33 (4 rows affected)
select type, avg(advance) from titles where advance > 1000 and advance < 10000 group by all type
type ------------ ------------------------ UNDECIDED NULL business 5,000.00 mod_cook NULL popular_comp 7,500.00 psychology 4,255.00 trad_cook 6,333.33 (6 rows affected)
The first statement produces groups only for those books that commanded advances of more than $1000 but less than $10,000. Since no modern cooking books have an advance within that range, there is no group in the results for the mod_cook type.
The second statement produces groups for all types, including modern cooking and “UNDECIDED,” even though the modern cooking group does not include any rows that meet the qualification specified in the where clause. Adaptive Server returns a NULL result for all groups that lack qualifying rows.
