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.
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.
SELECT
manager_name, AVG(salary) AS average |
GROUP BY Clause. For three given managers the average salary of all employees of associated with the respective manager is created.
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 depidcolumn 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.