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.
You can use a WHERE clause in a query containing a GROUP BY clause. Rows not meeting the conditions in the WHERE clause are eliminated before any grouping is done, for example:
USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type
Here is the result set:
Only rows with advances greater than $5,000 are included in the groups shown in the query results.
| type | ||
|---|---|---|
| business | 2.99 | |
| mod_cook | 2.99 | |
| popular_comp | 21.48 | |
| psychology | 14.30 | |
| trad_cook | 17.97 | |
| ** (5 row(s) affected) | ||
Sample Database All the examples on this page use the:
AdventureWorks Lightweight (LT) Sample Database.
Pubs Sample Database or
Northwind Sample Database.
if you don't have an instance of these databases running on your pc, please follow the links to download and install them.
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.
Note
