SQL/MP Report Writer Guide
Table Of Contents
- What’s New in This Manual
- About This Manual
- 1 Introduction to the NonStop SQL/MP Report Writer
- 2 Using SQLCI and the Report Writer
- 3 Selecting Data for a Report
- 4 Customizing a Report
- Defining the Layout
- Specifying the Items in a Detail Line
- Naming Select List and Detail Line Items
- Organizing Rows Into Break Groups
- Labeling Information
- Formatting Data Values
- Formatting Dates and Times
- Using TACL to Pass Parameters
- Conditional Printing of Items or Line Entries
- Redefining Special Characters
- Calculating Totals
- Calculating Subtotals
- Printing Double-Byte Characters
- A Comparison of the Report Writer and the Enform Language
- Index
Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-15
Grouping Data for Calculations
The ORDER BY clause is required when you want to specify break points in a report.
For more information, see Calculating Subtotals on page 4-51.
Grouping Data for Calculations
You can use aggregate functions to combine information from groups of rows and to
calculate values such as averages. Each group of rows results in one detail line of the
report.
Computing a Sum or Average
A typical application for grouping rows is to compute a sum or average. For example,
the following query groups rows of the ODETAIL table by PARTNUM and computes
the sum of the quantity ordered of each part:
Figure 3-4. Example of Rows Sorted by Two Columns
S> LIST ALL;
>> SELECT * FROM SALES.ODETAIL
+> WHERE QTY_ORDERED > 30
+> ORDER BY PARTNUM, QTY_ORDERED DESC;
ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED
-
70
2001600480
400410
700410
600480
-
-
400410
400410
800660
600480
400410
2001
2003
2003
6301
6400
6401
7301
7301
-
-
1000.00
1000.00
1900.00
1900.00
240.00
500.00
700.00
425.00
415.00
-
-
60
36
65
40
-
48
36
40
36
--- 15 row(s) selected.
VST0304.vsd