GROUP BY Example

Restriction: This topic applies to Windows environments only.

The following query uses functions to produce three output columns in the query results. The query calculates the number of parts made in each city, and the maximum and minimum weight of those parts (in each city):

SELECT city, COUNT(*), MIN(weight), MAX(weight)
FROM part
  GROUP BY city

The query results are as follows:

city	count(*)	min(weight)	max(weight)
LONDON	3	12	19
PARIS	3	12	17

The next example (not available in DB2 mode) finds the number of orders placed each month, grouping the results by the first output column:

SELECT XMONTH(o_date), COUNT(*)
FROM orders
  GROUP BY 1

The query results are as follows:

column1	count(*)
9	1
10	4