# Group by (SQL)

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Group_by_(SQL)
> Markdown URL: https://mediated.wiki/source/Group_by_(SQL).md
> Source: https://en.wikipedia.org/wiki/Group_by_(SQL)
> Source revision: 1323283523
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

{{Short description|SQL clause}}
A '''<code>GROUP BY</code>''' clause in [SQL](/source/SQL) specifies that a SQL <code>[SELECT](/source/Select_(SQL))</code> 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](/source/aggregate_function) for each group.<ref>{{Cite web|title=SQL GROUP BY Statement|url=https://www.w3schools.com/sql/sql_groupby.asp|access-date=2020-09-18|website=www.w3schools.com}}</ref><ref>{{Cite web|last=shkale-msft|title=GROUP BY (Transact-SQL) - SQL Server|url=https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql|access-date=2020-09-18|website=docs.microsoft.com|language=en-us}}</ref>

The result of a query using a <code>GROUP BY</code> clause contains one row for each group. This implies constraints on the columns that can appear in the associated <code>[SELECT](/source/Select_(SQL))</code> clause. As a general rule, the <code>[SELECT](/source/Select_(SQL))</code> clause may only contain columns with a unique value per group. This includes columns that appear in the <code>GROUP BY</code> clause as well as aggregates resulting in one value per group.<ref>{{Cite web|title=SQL Grouping and Aggregation|url=http://www.databaselecture.com/sql.html|access-date=2020-12-09|website=databaselecture.com|language=en-us}}</ref>

== Examples ==

Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.  
<syntaxhighlight lang="sql">
   SELECT DeptID, SUM(SaleAmount) FROM Sales
    WHERE SaleDate = '01-Jan-2000'
 GROUP BY DeptID
</syntaxhighlight>

In the following example one can ask "How many ''units'' were sold in each ''region'' for every ''ship date?''":

{| class="wikitable"
!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](/source/pivot_table) which answers the question "How many units were sold in each region for every ship date?":
<syntaxhighlight lang="sql">
   SELECT Region, Ship_Date, SUM(Units) AS Sum_of_Units
     FROM FlatData
 GROUP BY Region, Ship_Date
</syntaxhighlight>

== WITH ROLLUP ==
Since [SQL:1999](/source/SQL%3A1999), <code>GROUP BY</code> can be extended <code>WITH ROLLUP</code> to add a result line with a super-aggregator result. In the above example, it corresponds to the ''Grand total'' line.

== Common groupings ==
Common grouping ([aggregation](/source/Aggregate_function)) 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==
* [Aggregate function](/source/Aggregate_function)

==References==
<references />

==External links==
* [http://www.sqlsnippets.com/en/topic-13100.html SQL Snippets: SQL Features Tutorials - Grouping Rows with GROUP BY]

{{SQL}}

Category:SQL keywords
Category:Articles with example SQL code

{{database-stub}}
{{compu-lang-stub}}

---
Adapted from the Wikipedia article [Group by (SQL)](https://en.wikipedia.org/wiki/Group_by_(SQL)) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Group_by_(SQL)?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.