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.
Syntax
<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.
Example
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.
Example
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.