SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-19
Grouping Data for Calculations
>> VOLUME SALES;
>> SELECT P.PARTNUM, MIN(UNIT_PRICE), MAX(UNIT_PRICE)
+> FROM PARTS P, ODETAIL O
+> WHERE P.PARTNUM = O.PARTNUM
+> GROUP BY P.PARTNUM;
S> LIST NEXT 3;
PARTNUM (EXPR) (EXPR)
------- ------------ ------------
212 2450.00 2500.00
244 2800.00 3500.00
255 3800.00 4000.00
Determining Which Columns to Group
By specifying different sets of columns in the GROUP BY clause, you change the
results of the functions you apply to the group. Consider the following examples.
•
This example counts employees in departments numbered less than 2000.
Department 1000 has five employees.
>> SELECT DEPTNUM, COUNT(*)
+> FROM PERSNL.EMPLOYEE
+> WHERE DEPTNUM < 2000
+> GROUP BY DEPTNUM;
DEPTNUM (EXPR)
------- -------------------
1000 5
1500 4
--- 2 row(s) selected.
•
This example counts employees with the same job code in each department. In
department 1000, there are three employees with job code 500.
>> SELECT DEPTNUM, JOBCODE, COUNT(*)
+> FROM PERSNL.EMPLOYEE
+> WHERE DEPTNUM < 2000
+> GROUP BY DEPTNUM, JOBCODE;
DEPTNUM JOBCODE (EXPR)
------- ------- -------------------
1000 100 1
1000 500 3
1000 900 1
1500 100 1
1500 600 2
1500 900 1
--- 6 row(s) selected.
•
This example groups employees by job code and counts the employees with the
same job code in the same department. The result of this query is the same as the
previous one, but the rows are organized differently.
>> SELECT JOBCODE, DEPTNUM, COUNT(*)
+> FROM PERSNL.EMPLOYEE
+> WHERE DEPTNUM < 2000