SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
T-10
Examples—TOTAL
Totals require three report lines: two for underline characters (see
UNDERLINE_CHAR Option on page U-1) and one for the totals.

In calculating totals, the report writer uses the maximum format for the item's data
type and the same scale as the item to be totaled. In unusual cases (such as when
an expression contains an item multiplied by an extremely small fractional value),
this strategy can cause numeric overflow.
Specifying small numeric values in exponential notation (for example,
.0000246615 E0 instead of .0000246615) can prevent overflow by causing the
report writer to use a floating-point format for such calculations.
Examples—TOTAL

This example selects data, specifies a detail line, and specifies a column to total:
>> SELECT * FROM PERSNL.EMPLOYEE WHERE DEPTNUM = 3000;
S> DETAIL EMPNUM, SALARY;
S> TOTAL SALARY;
The output might look like:
EMPNUM SALARY
------ -----------
1 175500.00
...
-----------
-----------
489075.00

This example selects data, uses NAME to assign names to the last two columns in
the report, and then specifies totals for those columns:
>> SELECT PARTNUM, AVG(QTY_ORDERED),
AVG(UNIT_PRICE*QTY_ORDERED)
+> FROM SALES.ODETAIL
+> GROUP BY 1
+> ORDER BY 1;
S> NAME COL 2 AVGORD;
S> NAME COL 3 AVGPR;
S> TOTAL AVGORD, AVGPR;
The output might look like:
PARTNUM AVGORD AVGPR
------- ------------------- --------------------
...
7102 55 687.50
7301 3 4807.99
------------------- --------------------
------------------- --------------------
1480 55683.59