SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-33
Developing Multistep Queries
Multilevel Group Aggregates
Grouping Data for Calculations on page 3-15 describes ways to apply aggregate
functions to groups of rows. If you want to apply aggregate functions to multiple levels
of groups, you must specify more than one query and use temporary tables.
For example, suppose you want to report the average salary for each department and
within each department for each job classification. Follow these steps:
1. Create a temporary 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 )
+> CATALOG TEMPTABS ;
2. Insert the department number and average salary for each department in the
DEPTAVG table:
>> INSERT INTO DEPTAVG
+> (SELECT DEPTNUM, AVG(SALARY)
+> FROM PERSNL.EMPLOYEE
+> GROUP BY DEPTNUM);
3. Create another temporary 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 )
+> CATALOG TEMPTABS ;
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 PERSNL.EMPLOYEE
+> GROUP BY DEPTNUM, JOBCODE);
5. Join the temporary 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;