Summarizing Information › Summarizing Data In Groups

Summarizing Data In Groups

Groups

You can display summary information about groups. A group is a collection of detail lines that share a common value in one or more columns. For example, you can display summary salary information for each department. Summarizing information about groups in similar to break processing for those familiar with that reporting terminology.

Use GROUP BY

To summarize information for groups of values, use the GROUP BY clause. The GROUP BY clause indicates which columns to group. For example, this clause groups all rows that share the same department ID: group by deptid.

You can specify up to 31 columns in the GROUP BY clause. For example, you can group rows by department, and within the department, by job ID: group by deptid, jobid.

When you group rows, each column listed in the SELECT statement, except those named in the GROUP BY clause, must be acted upon by an aggregate function, such as AVG or MIN. For example, you might group rows by department ID to return average and minimum salaries for each department. CA OLQ displays one row for each group it evaluates.

Example 1— Grouping based on 1 column

List the number of employees in each department and the department's total and average salaries:

select deptid, count(*) as &xq.number of employees',
   sum(salary) as &xq.total salary',
   avg(salary) as &xq.average salary'
 from emp
 group by deptid ! display

                                   EMP REPORT
                                    dd/mm/yy

    DEPTID   NUMBER OF EMPLOYEES       TOTAL SALARY        AVERAGE SALARY
    ──────   ───────────────────   ─────────────────────   ──────────────
      3100                     9               348000.00         38666.66
      4000                     6               226000.00         37666.66
      6666                     4               496000.00        124000.00
     END OF REPORT

Example 2— Grouping based on 2 columns

For each department, list the number of employees, total salary, and average salary by gender. Notice that a sex code was entered incorrectly for an employee in department 6666:

select deptid, sex, count(*) as &xq.number of employees',
   sum(salary) as &xq.total salary',
   avg(salary) as &xq.average salary'
 from emp
 group by deptid, sex ! display

                                   EMP REPORT
                                    dd/mm/yy

   DEPTID  SEX  NUMBER OF EMPLOYEES      TOTAL SALARY       AVERAGE SALARY
   ──────  ───  ───────────────────  ─────────────────────  ──────────────
     3100  F                      3              135000.00        45000.00
     3100  M                      6              213000.00        35500.00
     4000  F                      3               94000.00        31333.33
     4000  M                      3              132000.00        44000.00
     6666  F                      1              240000.00       240000.00
     6666  M                      2              166000.00        83000.00
     6666  N                      1               90000.00        90000.00
     END OF REPORT