SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-33
Multilevel Group Aggregates
Multilevel Group Aggregates
Grouping Data for Calculations on page 3-16 describes ways to apply aggregate
functions to groups of rows. To apply aggregate functions to multiple levels of groups,
you must specify more than one query and use temporary tables.
For example, to report the average salary for each department and within each
department for each job classification:
1. Create an interim table to contain the department number and average salary for
each department. Use the INVOKE command to determine the data type for the
DEPTNUM column of the DEPT table. Then use CREATE TABLE to create the
temporary table:
>> CREATE TABLE DEPTAVG (
+> DEPTNUM NUMERIC (4) UNSIGNED NO DEFAULT,
+> AVGSAL NUMERIC (6) UNSIGNED NO DEFAULT )
+> ;
2. Insert the department number and average salary for each department in the
DEPTAVG table:
>> INSERT INTO DEPTAVG
+> (SELECT DEPTNUM, AVG(SALARY)
+> FROM EMPLOYEE
+> GROUP BY DEPTNUM);
3. Create another interim table to contain the job code and average salary for each
type of job within a department:
>> CREATE TABLE JOBAVG (
+> DEPTNUM NUMERIC (4) UNSIGNED NO DEFAULT,
+> JOBCODE NUMERIC (4) UNSIGNED NO DEFAULT,
+> AVGSAL NUMERIC (6) UNSIGNED NO DEFAULT )
+> ;
4. Insert the department number, job code, and average salary for each job in each
department in the JOBAVG table:
>> INSERT INTO JOBAVG
+> (SELECT DEPTNUM, JOBCODE, AVG(SALARY)
+> FROM EMPLOYEE
+> GROUP BY DEPTNUM, JOBCODE);
5. Join the interim tables and select the report information:
>> SET LIST_COUNT 0;
>> SELECT D.DEPTNUM, JOBCODE, D.AVGSAL, J.AVGSAL
+> FROM DEPTAVG D, JOBAVG J
+> WHERE D.DEPTNUM = J.DEPTNUM
+> ORDER BY D.DEPTNUM;
S> NAME COL 3 DEPT_AVGSAL;
S> NAME COL 4 JOB_AVGSAL;
S> DETAIL DEPTNUM, DEPT_AVGSAL, JOBCODE, JOB_AVGSAL;
S> BREAK ON DEPTNUM, DEPT_AVGSAL;
S> LIST N 8;