SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-35
Row Value as Percent of All Row Values
4. Select the report information from the DEPTTEMP table:
>> SELECT DEPTNUM, SUM(RANGE1), SUM(RANGE2), SUM(RANGE3)
+> FROM DEPTTEMP
+> GROUP BY DEPTNUM;
S> DETAIL DEPTNUM,
+> COL 2 AS I12 HEADING 'SAL. < 20000',
+> COL 3 AS I12 HEADING 'SAL. < 50000',
+> COL 4 AS I12 HEADING 'SAL. < 200000';
S> LIST ALL;
DEPTNUM SAL. < 20000 SAL. < 50000 SAL. < 200000
------- ------------ ------------ -------------
1000 1 3 5
1500 1 3 4
2000 0 4 5
. . . .
. . . .
4000 1 9 15
9000 0 1 2
--- 11 row(s) selected.
>>
By default, the range columns that receive no values from an INSERT command are
set to the system default value of zero, so they do not affect the final sums computed
for each column.
You can use this technique for other queries when you want to group rows and
compute aggregates based on different conditions.
Row Value as Percent of All Row Values
You can also use multiple step queries to compute what percent the current row value
is of all row values. For example, the next report displays the percent that an
individual's salary is of all salaries in a department. To produce the report:
1. Create an interim table to contain the average salary for each department:
>> CREATE TABLE AVGTEMP (
+> DEPTNUM NUMERIC (4) UNSIGNED NOT NULL,
+> AVGSAL NUMERIC (6) UNSIGNED NOT NULL)
+> ;
2. Insert the department number and average salary into the interim table:
>> INSERT INTO AVGTEMP
+> (SELECT DEPTNUM, AVG(SALARY) FROM EMPLOYEE
+> GROUP BY DEPTNUM);
3. Select the information for the report. Include an expression in the select list to
compute the percent of the department average:
>> SELECT E.DEPTNUM, EMPNUM, LAST_NAME, SALARY,
+> SALARY/AVGSAL*100.00