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-16
Grouping Data for Calculations
>> SELECT PARTNUM, SUM (QTY_ORDERED)
+> FROM SALES.ODETAIL
+> GROUP BY PARTNUM
+> ORDER BY PARTNUM;
S> LIST NEXT 2;
PARTNUM (EXPR)
------- -------------------
212 20
244 47
The GROUP BY clause determines the rows to which the function is applied. Each
group of rows with the same part number is processed to determine a sum.
You can use the following query to confirm which rows were grouped for parts 212 and
244 in the previous query:
>> SELECT PARTNUM, QTY_ORDERED
+> FROM SALES.ODETAIL
+> ORDER BY PARTNUM;
S> LIST N 8;
PARTNUM QTY_ORDERED
------- -----------
212 12
212 8
244 3
244 4
244 8
244 20
244 6
244 6
Consider the following points when using a GROUP BY clause or aggregate functions:
•
The select list can include only columns specified in the GROUP BY clause
(grouping columns), or the result of a function applied to a column. For example, if
you try to include UNIT_PRICE in the select list of the first of the preceding two
queries, an error message appears.
•
If you omit the GROUP BY clause from a SELECT command that includes a SUM
function, the sum of all retrieved rows is calculated; the group consists of the entire
result table. In this case, you must also omit PARTNUM from the select list
because it is no longer a grouping column.
•
You do not have to specify an ORDER BY clause when you are grouping rows.
You need the ORDER BY clause only if you want the rows that result from the
grouping to be arranged in a specific order. In the first of the preceding two
queries, you could specify ORDER BY PARTNUM to arrange the rows by part
number, or you could specify ORDER BY 2 to arrange the rows by the total
quantity ordered. Because SUM (QTY_ORDERED) does not have a name, you
specify 2 to indicate the second column in the select list.
In the second query, you use the ORDER BY clause to display all rows with the same
part number together. You cannot include a GROUP BY clause in this case because
you want to display each row of the group to see the values of QTY_ORDERED.