GROUP BY and ALL

Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement 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 have no 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:

USE pubs

SELECT type, AVG(price)        

FROM titles        

WHERE royalty = 10        

GROUP BY type        

  

Here is the result set:

type                                   

------------ --------------------------

business     17.31                     

popular_comp 20.00                     

psychology   14.14                     

trad_cook    17.97                     

  

(4 row(s) affected)

  

USE pubs

SELECT type, AVG(price)        

FROM titles        

WHERE royalty = 10        

GROUP BY ALL type        

  

Here is the result set:

type                                   

------------ --------------------------

business     17.31                     

mod_cook     (null)                    

popular_comp 20.00                     

psychology   14.14                     

trad_cook    17.97                     

UNDECIDED    (null)                    

  

(6 row(s) affected)

  

The first query produces groups only for those books that commanded royalties of 10 percent. Because no modern cookbooks have a royalty of 10 percent, there is no group in the results for the mod_cook type.

The second query 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.

See Also
ALL SELECT

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.