SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-19
Determining Minimum and Maximum Values
CUSTNUM CUSTNAME (EXPR)
------- ------------------ --------------------
21 CENTRAL UNIVERSITY 1
123 BROWN MEDICAL CO 2
143 STEVENS SUPPLY 1
You can also select and display only the grouped rows that have more than two orders
by moving the COUNT function to the WHERE clause as shown:
>> MODE REPORT;
>> SELECT C.CUSTNUM, CUSTNAME
+> FROM CUSTOMER C, ORDERS O
+> WHERE C.CUSTNUM = O.CUSTNUM
+> AND COUNT (DISTINCT ORDERNUM) >= 2
+> GROUP BY C.CUSTNUM, CUSTNAME;
S> LIST ALL;
CUSTNUM CUSTNAME
------- ------------------
123 BROWN MEDICAL CO
--- 1 row(s) selected.
This query produces the same result as the previous one by using a HAVING clause:
>> MODE REPORT;
>> SELECT C.CUSTNUM, CUSTNAME
+> FROM CUSTOMER C, ORDERS O
+> WHERE C.CUSTNUM = O.CUSTNUM
+> GROUP BY C.CUSTNUM, CUSTNAME
+> HAVING COUNT (DISTINCT ORDERNUM) >= 2;
A HAVING clause is similar to a WHERE clause, but the HAVING clause is applied to
the results of the GROUP BY clause. A column you specify in a HAVING clause must
be a grouping column or the argument of a function.
Determining Minimum and Maximum Values
You can use the MIN and MAX functions to determine a minimum and maximum value
in a column or of an expression. You can apply the function to a group of rows or to the
entire result of a SELECT command. For example, this query determines the minimum
and maximum unit price charged for each part in the current set of orders:
>> SELECT P.PARTNUM, MIN(UNIT_PRICE), MAX(UNIT_PRICE)
+> FROM PARTS P, ODETAIL O
+> WHERE P.PARTNUM = O.PARTNUM
+> GROUP BY P.PARTNUM;
S> LIST NEXT 3;
PARTNUM (EXPR) (EXPR)
------- ------------ ------------
212 2450.00 2500.00
244 2800.00 3500.00
255 3800.00 4000.00