SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-22
Using Expressions to Calculate Report Values
a part, more than one row of the PARTSUPP might describe the same part number
and supplier. To count the number of distinct suppliers of each part, use this query:
>> SELECT PARTNUM, COUNT ( DISTINCT SUPPNUM )
+> FROM PARTSUPP
+> GROUP BY PARTNUM;
S> LIST NEXT 2;
PARTNUM (EXPR)
------- ----------------------
212 2
244 2
S>
•
This query counts the number of orders taken by each sales representative who
has more than one customer with current orders:
>> SELECT SALESREP, COUNT (DISTINCT ORDERNUM)
+> FROM ORDERS OX
+> GROUP BY SALESREP
+> HAVING EXISTS (SELECT SALESREP
+> FROM ORDERS
+> WHERE OX.SALESREP = SALESREP
+> AND COUNT (DISTINCT CUSTNUM) > 1
+> GROUP BY SALESREP);
SALESREP (EXPR)
-------- -------------------
220 3
226 3
If you specify DISTINCT preceding the select list, you cannot specify it in any
aggregate function in the select list or in any predicate of the WHERE or HAVING
clause.
Using Expressions to Calculate Report Values
You can calculate values for items in a report output line. If you are printing the report
in the default report format, you specify the calculation in the select list.
The query in Figure 3-5 includes two expressions in the select list. The first expression
calculates the total part cost for the available quantity of each part. The second
expression calculates the profit (price minus cost) for the available quantity of each
part.