SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-66
Computing Row Value as a Percent of All Row
Values
>> 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 ;
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.
Computing Row Value as a Percent of All Row Values
You can also use multistep queries to compute what percent the current row value is of
all row values. For example, these report displays the percent that an individual’s
salary is of all salaries in a department. The report is produced with these steps: