SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-21
Using Expressions to Calculate Report Values
The PARTSUPP table contains rows that record a part number, the supplier number,
the part cost, and the quantity received. If the supplier changes the cost of a part, more
than one row of the PARTSUPP might describe the same part number and supplier. If
you want to count the number of distinct suppliers of each part, you can use the
following query:
>> SELECT PARTNUM, COUNT ( DISTINCT SUPPNUM )
+> FROM INVENT.PARTSUPP
+> GROUP BY PARTNUM;
S> LIST NEXT 2;
PARTNUM (EXPR)
------- ----------------------
212 2
244 2
S>
The next 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 SALES.ORDERS OX
+> GROUP BY SALESREP
+> HAVING EXISTS (SELECT SALESREP
+> FROM SALES.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.