SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-18
Counting Rows
the select list and to the GROUP BY clause. The same rows will be grouped. You
must qualify the PARTNUM column or the reference will be ambiguous.
>> SELECT O.PARTNUM, PARTDESC, SUM (QTY_ORDERED)
+> FROM ODETAIL O, PARTS P
+> WHERE O.PARTNUM = P.PARTNUM
+> GROUP BY O.PARTNUM, PARTDESC;
S> LIST NEXT 2;
PARTNUM PARTDESC (EXPR)
------- ------------------ -------------------
212 PC SILVER, 20 MB 20
244 PC GOLD, 30 MB 47
•
You can also include PRICE and QTY_AVAILABLE in the select list and GROUP
BY clause without changing the formation of the groups. However, if you include a
column from the ODETAIL table, the number of groups increases because
ORDERNUM, UNIT_PRICE, and QTY_ORDERED have different values for the
same part number.
The same type of query computes an average. The next query calculates both the
average price and total quantity ordered of each part. The default heading for an
expression is (EXPR).
>> SELECT PARTNUM, AVG (UNIT_PRICE), SUM(QTY_ORDERED)
+> FROM ODETAIL
+> ORDER BY PARTNUM
+> GROUP BY PARTNUM;
S> LIST N 3;
PARTNUM (EXPR) (EXPR)
------- -------------------- --------------------
212 2475.00 20
244 3216.00 47
255 3900.00 38
S>
If you omit PARTNUM from the select list and you omit the GROUP BY clause, the
query computes an average unit price and sums the quantity ordered using all
rows selected. The result is one row of output.
Counting Rows
You can count all rows or distinct rows in a group. Suppose that you want to know the
names of all customers who have at least two current orders placed.
One way to do this is to join the CUSTOMER and ORDERS tables, group the rows by
customer number and customer name, and display the count of all current orders. By
examining the report, you can locate groups that have at least two orders (for example,
BROWN MEDICAL CO):
>> SELECT C.CUSTNUM, CUSTNAME, COUNT (DISTINCT ORDERNUM)
+> FROM CUSTOMER C, ORDERS O
+> WHERE C.CUSTNUM = O.CUSTNUM
+> GROUP BY C.CUSTNUM, CUSTNAME;
S> LIST N 3;