SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-20
Determining Which Columns to Group
Determining Which Columns to Group
By specifying different sets of columns in the GROUP BY clause, you change the
results of functions you apply to the group. For example:
•
This example counts employees in departments numbered less than 2000.
Department 1000 has five employees.
>> SELECT DEPTNUM, COUNT(*)
+> FROM 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 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 EMPLOYEE
+> WHERE DEPTNUM < 2000
+> GROUP BY JOBCODE, DEPTNUM;
JOBCODE DEPTNUM (EXPR)
------- ------- -------------------
100 1000 1
100 1500 1
500 1000 3
600 1500 2
900 1000 1
900 1500 1