GROUP BY Clause

A GROUP BY clause is a part of a query. It defines grouping columns used to explicitly group a result table. The result of the GROUP BY clause is called a grouped table . To filter the results, use the HAVING clause .

<group by clause> ::= GROUP BY <column reference> ( ',' <column reference> )*.

Every <column reference> in the <group by clause> must reference exactly one column of the table to group. You can specify several column references in a single GROUP BY clause. The referenced columns are the grouping columns of the grouped table.

Note

You must always refer to a column using the <column reference>, which is either the <column name> or the qualified name (<table name>.<column name>). Even if you have specified a <column alias> for a column in the select list, you must not use it in the GROUP BY clause but refer to the column using the <column reference>. For example, if you select a column named depid as DEPARTMENT_ID in the result table, and group the result by this column, you should use the name depid in the GROUP BY clause.

Examples

Sample Code

SELECT manager_name, AVG(salary) AS average 
               FROM employees AS e JOIN managers AS m 
               ON e.manager_id = m.manager_id 
               WHERE manager_name IN ('Paul Smith', 'Mary Jones', 'John Miles') 
               GROUP BY manager_name

GROUP BY Clause. For three given managers the average salary of all employees of associated with the respective manager is created.

Sample Code

SELECT d.depid, d.name,  COUNT(*) AS EMPLOYEE_COUNT 
                  FROM department d JOIN employee e
                  ON d.DEPID = e.DEPID                                                                                                  
                  GROUP BY d.depid, d.name

GROUP BY Clause. The statement returns the number of employees in each department with at least one employee. Note that you cannot group the result by the depid column only, but by both depid and name . In this statement you are required to use the GROUP BY statement because in a simple select statement you cannot combine a normal column with an aggregation.

More Information

Select List