A
GROUP BY statement in
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
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
In database management, an aggregate function or aggregation function is a function where multiple values are processed together to form a single summary statistic.
Common aggregate functions include:
* Average (i.e., arithmetic mean)
* Count ...
for each group.
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.
Examples
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?''":
The following code returns the data of the above
pivot table
A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories. The aggregatio ...
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
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:
* 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
In database management, an aggregate function or aggregation function is a function where multiple values are processed together to form a single summary statistic.
Common aggregate functions include:
* Average (i.e., arithmetic mean)
* Count ...
References
External links
SQL Snippets: SQL Features Tutorials - Grouping Rows with GROUP BY
SQL keywords
Articles with example SQL code
{{compu-lang-stub