Group by (SQL)
A GROUP BY clause in SQL specifies that a SQL SELECT statement partitions result rows into groups, based on their values in one or several columns.
≈ 3 min read
Names in other languages
No names in other languages are held for this entry yet.
Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.
A GROUP BY clause in SQL specifies that a SQL SELECT statement partitions result rows into groups, based on their values in one or several columns. Typically, grouping is used to apply some sort of aggregate function for each group.[1][2]
The result of a query using a GROUP BY clause contains one row for each group. This implies constraints on the columns that can appear in the associated SELECT clause. As a general rule, the SELECT clause may only contain columns with a unique value per group. This includes columns that appear in the GROUP BY clause as well as aggregates resulting in one value per group.[3]
Examples
edit edit sourceReturns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.
SELECT DeptID, SUM(SaleAmount) FROM Sales WHERE SaleDate = '01-Jan-2000' GROUP BY DeptIDIn the following example one can ask "How many units were sold in each region for every ship date?":
| Sum of units | Ship date ▼ | |||||
|---|---|---|---|---|---|---|
| Region ▼ | 2005-01-31 | 2005-02-28 | 2005-03-31 | 2005-04-30 | 2005-05-31 | 2005-06-30 |
| East | 66 | 80 | 102 | 116 | 127 | 125 |
| North | 96 | 117 | 138 | 151 | 154 | 156 |
| South | 123 | 141 | 157 | 178 | 191 | 202 |
| West | 78 | 97 | 117 | 136 | 150 | 157 |
| (blank) | ||||||
| Grand total | 363 | 435 | 514 | 581 | 622 | 640 |
The following code returns the data of the above pivot table which answers the question "How many units were sold in each region for every ship date?":
SELECT Region, Ship_Date, SUM(Units) AS Sum_of_Units FROM FlatData GROUP BY Region, Ship_DateWITH ROLLUP
edit edit sourceSince SQL:1999, GROUP BY can be extended WITH ROLLUP to add a result line with a super-aggregator result. In the above example, it corresponds to the Grand total line.
Common groupings
edit edit sourceCommon grouping (aggregation) functions include:
- Count(expression) - Quantity of matching records (per group)
- Sum(expression) - Summation of given value (per group)
- Min(expression) - Minimum of given value (per group)
- Max(expression) - Maximum of given value (per group)
- Avg(expression) - Average of given value (per group)
See also
edit edit sourceReferences
edit edit source- ^ "SQL GROUP BY Statement". www.w3schools.com. Retrieved 2020-09-18. Wikipedia revision 1323283523, retrieved 2026-11-18
- ^ shkale-msft. "GROUP BY (Transact-SQL) - SQL Server". docs.microsoft.com. Retrieved 2020-09-18. Wikipedia revision 1323283523, retrieved 2026-11-18
- ^ "SQL Grouping and Aggregation". databaselecture.com. Retrieved 2020-12-09. Wikipedia revision 1323283523, retrieved 2026-11-18
External links
edit edit source SQLText derived from https://en.wikipedia.org/w/index.php?oldid=1323283523 under CC BY-SA 4.0; modifications have been made.