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]
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]
Returns 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 DeptID
In 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_Date
Common grouping ( aggregation) functions include:
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]
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]
Returns 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 DeptID
In 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_Date
Common grouping ( aggregation) functions include: