SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-7
Organizing Results
The GROUP BY Clause
The GROUP BY clause groups rows with the same value and returns one row per
group. The GROUP BY clause, like the DISTINCT clause, removes duplicate rows
from the result, as well as performing other functions.
To show how the GROUP BY clause works, consider the query from the previous
subsection:
SELECT PARTNUM FROM ODETAIL ;
This query returns 72 rows, with part numbers 212 and 244 appearing several times in
the result.
If you specify a GROUP BY clause on the PARTNUM column, as shown in
Example 1-4, the query returns the same result as if you had specified a DISTINCT
clause.
The GROUP BY clause is powerful because you can use it to combine information
from groups of rows for processing by aggregate functions. For example, you might
want to calculate values such as sums and averages.
The GROUP BY clause does not imply ordering; to request a specific order, use the
ORDER BY clause.
In Example 1-5, the GROUP BY clause determines the rows to which the SUM
function is applied. Each row with the same part number has been grouped and the
SUM function applied to the values in the QTY_ORDERED column.
Example 1-4. SELECT Statement With GROUP BY Clause
SELECT PARTNUM FROM ODETAIL
GROUP BY PARTNUM ;
PARTNUM
-------
244
.
212
.
.
7301
--- 27 row(s) selected.