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-17
Grouping Data for Calculations
•
In summary, use the ORDER BY clause to arrange rows in sequence; use the
GROUP BY clause to combine values and create one row from a group of rows.
•
If you want to include the part description in the first query, you can join the
ODETAIL and PARTS tables. Because rows in the result table for a specific part
number all contain the same part description, you can include the part description
as a grouping column without changing the result. Add the column name
PARTDESC to the select list and to the GROUP BY clause. The same rows will be
grouped. You must qualify the PARTNUM column or the reference will be
ambiguous.
>> SELECT O.PARTNUM, PARTDESC, SUM (QTY_ORDERED)
+> FROM SALES.ODETAIL O, SALES.PARTS P
+> WHERE O.PARTNUM = P.PARTNUM
+> GROUP BY O.PARTNUM, PARTDESC;
S> LIST NEXT 2;
PARTNUM PARTDESC (EXPR)
------- ------------------ -------------------
212 PC SILVER, 20 MB 20
244 PC GOLD, 30 MB 47
You can also include PRICE and QTY_AVAILABLE in the select list and GROUP BY
clause without changing the formation of the groups. However, if you include a column
from the ODETAIL table, the number of groups will increase because ORDERNUM,
UNIT_PRICE, and QTY_ORDERED have different values for the same part number.
The same type of query computes an average. The next query calculates both the
average price and total quantity ordered of each part. The default heading for an
expression is (EXPR).
>> SELECT PARTNUM, AVG (UNIT_PRICE), SUM(QTY_ORDERED)
+> FROM SALES.ODETAIL
+> ORDER BY PARTNUM
+> GROUP BY PARTNUM;
S> LIST N 3;
PARTNUM (EXPR) (EXPR)
------- -------------------- --------------------
212 2475.00 20
244 3216.00 47
255 3900.00 38
S>
If you omit PARTNUM from the select list, and you omit the GROUP BY clause, the
query computes an average unit price and sums the quantity ordered using all rows
selected. The result is one row of output.
Counting Rows
You can count all rows or distinct rows in a group. Suppose you want to know the
names of all customers who have at least two current orders placed.
One way to do this is to join the CUSTOMER and ORDERS tables, group the rows by
customer number and customer name, and display the count of all current orders. By