GROUP BY clause

GROUP BY clause A GROUP BY clause, part of a selectExpression, groups a result into subsets that have matching values for one or more columns. GROUP BY clause

In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes. See for more information.

You typically use a GROUP BY clause in conjunction with an aggregate expression.

Using the ROLLUP syntax, you can specify that multiple levels of grouping should be computed at once.

Syntax GROUP BY { columnName [ , columnName ]* | ROLLUP ( columnName [ , columnName ]* ) }

The columnName must be a column from the current scope of the query; there can be no columns from a query block outside the current scope. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query.

The selectItems in the selectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

Examples -- find the average flying_times of flights grouped by -- airport SELECT AVG (flying_time), orig_airport FROM Flights GROUP BY orig_airport SELECT MAX(city_name), region FROM Cities, Countries WHERE Cities.country_ISO_code = Countries.country_ISO_code GROUP BY region -- group by an a smallint SELECT ID, AVG(SALARY) FROM SAMP.STAFF GROUP BY ID -- Get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column using the AS clause -- And group by the WORKDEPT column using the correlation name OTHERS SELECT OTHERS.WORKDEPT AS DEPTNO, AVG(OTHERS.SALARY) AS AVGSALARY, COUNT(*) AS EMPCOUNT FROM SAMP.EMPLOYEE OTHERS GROUP BY OTHERS.WORKDEPT -- Compute sub-totals of Sales_History data, grouping it by Region, by -- (Region, State), and by (Region, State, Product), as well as computing -- an overall total of the sales for all Regions, States, and Products: SELECT Region, State, Product, SUM(Sales) Total_Sales FROM Sales_History GROUP BY ROLLUP(Region, State, Product)