SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-18
Grouping Data for Calculations
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 SALES.CUSTOMER C, SALES.ORDERS O
+> WHERE C.CUSTNUM = O.CUSTNUM
+> GROUP BY C.CUSTNUM, CUSTNAME;
S> LIST N 3;
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:
>> SELECT C.CUSTNUM, CUSTNAME
+> FROM SALES.CUSTOMER C, SALES.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.
The next query produces the same result as the previous one by using a HAVING
clause:
>> 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, the following query determines the
minimum and maximum unit price charged for each part in the current set of orders: