SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-17
Grouping Data for Calculations
The GROUP BY clause determines the rows to which the function is applied. Each
group of rows with the same part number is processed to determine a sum.
You can use this query to confirm which rows were grouped for parts 212 and 244 in
the previous query:
>> SELECT PARTNUM, QTY_ORDERED
+> FROM ODETAIL
+> ORDER BY PARTNUM;
S> LIST N 8;
PARTNUM QTY_ORDERED
------- -----------
212 12
212 8
244 3
244 4
244 8
244 20
244 6
244 6
In the second query, you use the ORDER BY clause to display all rows with the same
part number together. You cannot include a GROUP BY clause in this case because
you want to display each row of the group to see the values of QTY_ORDERED.
When using a GROUP BY clause or aggregate function, consider these points:
•
The select list can include only columns specified in the GROUP BY clause
(grouping columns) or the result of a function applied to a column. For example, if
you try to include UNIT_PRICE in the select list of the first of the preceding two
queries, an error message appears.
•
If you omit the GROUP BY clause from a SELECT command that includes a SUM
function, the sum of all retrieved rows is calculated. The group consists of the
entire result table. In this case, you must also omit PARTNUM from the select list
because it is no longer a grouping column.
•
You do not have to specify an ORDER BY clause when you are grouping rows.
You need the ORDER BY clause only if you want the rows that result from the
grouping to be arranged in a specific order. In the first of the preceding two
queries, you could specify ORDER BY PARTNUM to arrange the rows by part
number, or you could specify ORDER BY 2 to arrange the rows by the total
quantity ordered. Because SUM (QTY_ORDERED) does not have a name, you
specify 2 to indicate the second column in the select list.
•
Use the ORDER BY clause to arrange rows in sequence. Use the GROUP BY
clause to combine values and create one row from a group of rows.
•
To include the part description in the first query, you can join the ODETAIL and
PARTS tables. Because rows in the result table for a specific part number all
contain the same part description, you can include the part description as a
grouping column without changing the result. Add the column name PARTDESC to