Show TOC Start of Content Area

Syntax documentation GROUP BY Clause  Locate the document in its SAP Library structure

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

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.

Examples

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

More Information

Select List

End of Content Area