The GROUP BY Clause specifies the groups into which output rows are to be placed. It also determines if aggregate functions are included in the SELECT clause (select list), and calculates a summary value for each group.
In other words, whenever the SELECT clause of your SELECT statement includes column values and aggregate functions, you must include the GROUP BY clause. The GROUP BY clause must list all of the column values used in the SELECT clause.
How to Use 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.
///////////////////////////// Delete Below /////////////////////////////
Grouping Rules you must know
1. Every non-aggregate column that appears in the SELECT clause must also appear in the GROUP BY clause.
2. You may not use aliases in the HAVING clause. MySQL allows usage of aliases in the HAVING clause, but you may want to avoid this to keep your code as cross-database compatible as possible.
3. You may use aliases in the ORDER BY clause.
4. To filter rows based on aggregate functions, use the HAVING clause.
5. You should declare column aliases for any calculated fields in the SELECT clause.
6. You may not use aggregate functions in the WHERE clause.
///////////////////////////////////////////////
Related Content
