Group by (SQL)

{{Short description|SQL clause}} A '''<code>GROUP BY</code>''' clause in SQL specifies that a SQL <code>SELECT</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 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</code> clause. As a general rule, the <code>SELECT</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 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, <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) 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

==References== <references />

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

Category:SQL keywords Category:Articles with example SQL code