Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement in which it is used also includes a WHERE clause.
If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups don't 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 are two examples:
SELECT type, AVG(price) |
|||||
FROM titles |
|||||
WHERE royalty = 10 |
|||||
GROUP BY type |
|||||
type |
|||||
------------ |
----------- |
||||
business |
17.31 |
||||
popular_comp |
20.00 |
||||
psychology |
40.38 |
||||
trad_cook |
17.97 |
||||
(4 row(s) affected) |
|||||
SELECT type, AVG(price) |
|||||
FROM titles |
|||||
WHERE royalty = 10 |
|||||
GROUP BY all type |
|||||
type |
|||||
--------- |
----------- |
||||
business |
17.31 |
||||
mod_cook |
(null) |
||||
popular_comp |
20.00 |
||||
psychology |
40.38 |
||||
trad_cook |
17.97 |
||||
UNDECIDED |
(null) |
||||
(6 row(s) affected) |
|||||
The first statement produces groups only for those books that commanded royalties of 10 percent. Since no modern cookbooks have a royalty of 10 percent, there is no group in the results for the mod_cook type.
The second statement produces groups for all types, including modern cookbooks and UNDECIDED, even though the modern cookbook group doesn't include any rows that meet the qualification specified in the WHERE clause.
The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.