Group by (SQL)

Last updated

A GROUP BY statement 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]

Contents

The result of a query using a GROUP BY statement 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

Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.

SELECTDeptID,SUM(SaleAmount)FROMSalesWHERESaleDate='01-Jan-2000'GROUPBYDeptID

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

Sum of unitsShip date ▼
Region ▼2005-01-312005-02-282005-03-312005-04-302005-05-312005-06-30
East6680102116127125
North96117138151154156
South123141157178191202
West7897117136150157
(blank)
Grand total363435514581622640

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?":

SELECTRegion,Ship_Date,SUM(Units)ASSum_of_UnitsFROMFlatDataGROUPBYRegion,Ship_Date

WITH ROLLUP

Since 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

Common grouping (aggregation) functions include:

See also

References

  1. "SQL GROUP BY Statement". www.w3schools.com. Retrieved 2020-09-18.
  2. shkale-msft. "GROUP BY (Transact-SQL) - SQL Server". docs.microsoft.com. Retrieved 2020-09-18.
  3. "SQL Grouping and Aggregation". databaselecture.com. Retrieved 2020-12-09.