Group by (SQL)

A GROUP BY statement in SQL specifies that a SQL [[Select (sql'(pare)'''Bold text'Bold text'</ref>)|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.

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

Returns the data of the example pivot table which answers the question "How many Units did we sell 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:

• 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

External links

• SQL Snippets: SQL Features Tutorials - Grouping Rows with GROUP BY
• MySQL GROUP BY and HAVING Clause with Examples