# 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. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.

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

#### Syntax

    GROUP BY column-Name [ , column-Name ] *

column-Name 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.

SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

    -- 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

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause