You can display a report that gives a total or average across the rows in a report by using column usage codes. Totals and averages are examples of QMF's aggregation functions, which are any functions that summarize the data in a column. You can also specify other aggregation usages, such as standard deviation, percent, or cumulative totals. For more information on aggregation functions, see DB2 QMF Reference.
In this example, you use the ACROSS, GROUP, SUM, and OMIT usage codes to create a report that summarizes the salary, commission, and total earnings for each job description within every department.
To summarize report data:
For this example, run the following SQL query:
SELECT NAME, DEPT, JOB, SALARY, COMM, SALARY + COMM FROM Q.STAFF WHERE DEPT IN (15, 20, 38) AND JOB <> 'MGR' ORDER BY DEPT, JOB
SHOW FORM.COLUMNSThe FORM.COLUMNS panel displays.
For this example, change the column name that is created by the query to TOTAL_EARNINGS.
FORM.COLUMNS MODIFIED NUM COLUMN HEADING USAGE INDENT WIDTH EDIT SEQ --- ---------------------------------------- ------- ------ ----- ----- --- 1 NAME OMIT 2 9 C 1 2 DEPT GROUP 2 6 L 2 3 JOB ACROSS 2 5 C 3 4 SALARY SUM 2 11 D2 4 5 COMM SUM 2 10 D2 5 6 TOTAL_EARNINGS SUM 2 12 D2 6 *** END ***
SHOW FORM.OPTIONSThe FORM.OPTIONS panel displays.
<------------------------------------ JOB --------------------------- <--------------- CLERK ---------------> <--------------- SALES ----- SUM SUM SUM TOTAL SUM SUM DEPT SALARY COMM EARNINGS SALARY COMM ------ ----------- ---------- ------------ ----------- ---------- - 15 $24,766.70 $316.70 $25,083.40 $16,502.83 $1,152.00 20 $27,757.35 $254.70 $28,012.05 $18,171.25 $612.45 38 $24,964.50 $416.50 $25,381.00 $34,814.30 $1,496.80 =========== ========== ============ =========== ========== = $77,488.55 $987.90 $78,476.45 $69,488.38 $3,261.25