|
<< Click to Display Table of Contents >> GROUP BY clause |
  
|
|
<< Click to Display Table of Contents >> GROUP BY clause |
|
Combines rows with column values in common into single rows.
GROUP BY column_reference [, column reference...]
Use a GROUP BY clause to cause an aggregation process to be repeated once for each group of similar rows. Similarity between rows is determined by the distinct values (or combination of values) in the columns specified in the GROUP BY. For instance, a query with a SUM function produces a result set with a single row with the total of all the values for the column used in the SUM function. But when a GROUP BY clause is added, the SUM function performs its summing action once for each group of rows, a subtotalling. In statements that support a GROUP BY clause, the use of a GROUP BY clause is optional. A GROUP BY clause becomes necessary when both aggregated and non-aggregated columns are included in the same SELECT statement.
In the statement below, the SUM function produces one subtotal of the ItemsTotal column for each distinct value in the CustNo column (i.e., one subtotal for each different customer).
SELECT CustNo, SUM(ItemsTotal)
FROM Orders
GROUP BY CustNo
The value for the GROUP BY clause is a comma-separated list of columns. Each column in this list must meet the following criteria:
•Be in one of the tables specified in the FROM clause of the query.
•Also be in the SELECT clause of the query.
•Cannot have an aggregate function applied to it (in the SELECT clause).
When a GROUP BY clause is used, all table columns in the SELECT clause of the query must meet at least one of the following criteria, or it cannot be included in the SELECT clause:
•Be in the GROUP BY clause of the query.
•Be in the subject of an aggregate function.
Literal values in the SELECT clause are not subject to the preceding criteria and are not required to be in the GROUP BY clause in addition to the SELECT clause.
The distinctness of rows is based on the columns in the column list specified. All rows with the same values in these columns are combined into a single row (or logical group). Columns that are the subject of an aggregate function have their values across all rows in the group combined. All columns not the subject of an aggregate function retain their value and serve to distinctly identify the group. For example, in the SELECT statement below, the values in the Sales column are aggregated (totalled) into groups based on distinct values in the Company column. This produces total sales for each company.
SELECT C.Company, SUM(O.ItemsTotal) AS TotalSales
FROM Customer C, Orders O
WHERE C.CustNo = O.CustNo
GROUP BY C.Company
ORDER BY C.Company
A column may be referenced in a GROUP BY clause by a column correlation name, instead of actual column names. The statement below forms groups using the first column, Company, represented by the column correlation name Co.
SELECT C.Company Co, SUM(O.ItemsTotal) AS TotalSales
FROM Customer C, Orders O
WHERE C.CustNo = O.CustNo
GROUP BY Co
ORDER BY 1
| Note | Derived values (calculated values) cannot be used as the basis for a GROUP BY clause. |
Applicability
SELECT statements (when data aggregation is performed)
