SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-34
Conditional Aggregates
DEPTNUM DEPT_AVGSAL JOBCODE JOB_AVGSAL
------- ----------- ------- ----------
1000 52000 100 137000
500 34666
900 19000
1500 41250 100 90000
600 29000
900 17000
2000 50000 100 13800
200 24000
S>
The BREAK ON command suppresses printing of the same department number and
salary average in multiple lines. You can drop the tables or purge the data and reuse
the tables in future reports.
Conditional Aggregates
To produce a report that contains aggregate function values calculated for groups
selected by different WHERE clause criteria, use a multiple step query. For example,
suppose that a report counts the number of employees whose salaries are in these
ranges:
Range 1 < 20000
Range 2 < 50000
Range 3 < 200000
The report contains one detail line for each department.
To create the report:
1. Create an interim table for the report values. Define the RANGE n columns with
the system default so that you do not have to insert a value:
>> CREATE TABLE DEPTTEMP (
+> DEPTNUM NUMERIC (4) UNSIGNED NO DEFAULT,
+> RANGE1 INTEGER UNSIGNED DEFAULT SYSTEM,
+> RANGE2 INTEGER UNSIGNED DEFAULT SYSTEM,
+> RANGE3 INTEGER UNSIGNED DEFAULT SYSTEM )
+> ;
2. Insert the count for RANGE1 in the DEPTTEMP table:
>> INSERT INTO DEPTTEMP (DEPTNUM, RANGE1)
+> (SELECT DEPTNUM, COUNT(*) FROM EMPLOYEE
+> WHERE SALARY < 20000
+> GROUP BY DEPTNUM );
3. Enter two more INSERT commands to insert the counts for RANGE2 and
RANGE3. For each command, substitute RANGE2 or RANGE3 in the select list
and change the value in the WHERE clause to 50000 first and then to 200000.