SELECT...GROUP BY - Group derived rows

You use the GROUP BY clause to combine table rows into groups. Two rows belong to the same group if, for each grouping column, the values in both rows are the same with regard to the comparison rules (see section "Comparison of two rows"), or both values are the NULL value.

The derived table contains a row for each group.

---

SELECT ...

GROUP BY column,...

---

column

Grouping column. column must be part of a table that was specified in the FROM clause. Ambiguous column names must be qualified with the table name. If you declared a correlation name for the table involved in the FROM clause, you must use this name to qualify the column names.

Multiple columns cannot be used as the grouping column.

Effect of the GROUP BY clause

If you specify the GROUP BY clause, only columns listed in GROUP BY or which are arguments in an aggregate function can be included in the SELECT list.

Aggregate functions for columns of a grouped table are evaluated for each group.

How are groups created?

• A group is a set of rows that all have the same values in each specified grouping column according to the comparison rules.

• Rows that have the NULL value in the same column and the same values in the other columns also constitute a group.

Examples

List the average amount of VAT for each order number:

SELECT order_num, AVG(vat) FROM service GROUP BY order_num
   order_num
   200          0.14
   211          0.06
   250          0.07

The number of contacts is determined for all customers outside the USA and grouped by customer number. If the GROUP BY clause is specified, only those columns may occur in the select list which are specified in the GROUP BY clause or which are arguments of an aggregate function. The derived table for the SELECT expression contains one row for each group.

SELECT contacts.cust_num, COUNT(*) AS total FROM contacts, customers 
   WHERE contacts.cust_num = customers.cust_num AND customers.country <>'USA' 
   GROUP BY contacts.cust_num

Derived table

cust_num	number
100	2
101	1
102	1
103	1
104	1
105	1

When the SELECT expression is supplemented by the HAVING clause below (see the next section), the derived table only contains the first row.

HAVING COUNT(*) > 1