COMPUTE ... GROUP BY

Commands and Syntax › COMPUTE ... GROUP BY

COMPUTE ... GROUP BY

COMPUTE GROUP BY performs computations on fields in a report file. Computed fields can be manipulated similarly to database records.

These computations are then displayed at break points that have been defined by the GROUP BY clause.

Syntax:

►►─── COMpute ────────────────────────────────────────────────────────────────►

 ►─┬───────────────────────────────────────────────────────────────┬──────────►
   └─ REPort ─┬─────┬─ report-name ─┬────────────────────────────┬─┘
              └─ = ─┘               └─ USEr ─┬─────┬─ user-name ─┘
                                             └─ = ─┘

 ►─── compute-name = expression ──────────────────────────────────────────────►

 ►─── GROup by ─┬─ group-by-expression ─┬─────────────────────────────────────►
                └─ ALL ─────────────────┘

 ►─┬─────────────────────────┬────────────────────────────────────────────────►
   └─ HAVing ─┬─ criteria ─┬─┘
              └─ ALL ──────┘

 ►─┬───────────┬──────────────────────────────────────────────────────────────►◄
   └─ LEVel n ─┘

Parameters:

REPORT= report-name

Identifies the saved report for which the computation is performed. If you don't specify the name of the report, the computations are performed on fields in the current report.

USER=user-name— Identifies the user ID of the report owner. If you don't specify the user ID, the report is retrieved from the current user's directory.

compute-name

Specifies the field name to be used to reference the computation in any of the OLQ reporting functions (that is, DISPLAY COLS=, EDIT, SORT). A compute-name that contains embedded blanks or special characters must be enclosed in quotation marks.

expression

Defines the computations used to create the new column.

Expression typically contains an aggregate function.

GROUP BY

Specifies a break will occur.

group-by-expression — Specifies the field to break on; can be any compute expression that does not contain an aggregate function
ALL—Final break processing for the entire report

HAVING

Applies selection criteria to the groupings of data values determined by the GROUP BY expression.

criteria—Restricts the groups displayed; can contain aggregate functions; for example, HAVING AVE(SALARY) > 40000
ALL—Specifies that no selection criteria on groups should be applied

Note: For more information about HAVING clause, see FIND / GET Selection Criteria.

LEVEL n

Specifies the break level, in numeric order, with 1 representing the highest level. For example, if LEVEL 1 is specified, a break at the highest level causes a break at each subsequent lower level. The default n value is 1.

Examples:

The following example uses the report built by the SELECT statement:

select dept-id-0410,emp-last-name-0415,salary-amount-0420
 from department,employee,emposition
 where dept-employee and emp-emposition

Compute Total Salary

COMPUTE commands are used to define the computation of the 'TOTAL SALARY' field:

compute 'total salary' = sum(salary-amount-0420) group by dept-id-0410 ! display DEPARTMENT/EMPLOYEE/EMPOSITION REPORT mm/dd/yy DEPT-ID-0410 EMP-LAST-NAME-0415 SALARY-AMOUNT-0420 6666 HENDON 240000.00 6666 PAPAZEUS 100000.00 6666 PAPAZEUS 90000.00 6666 RUPEE 80000.00 6666 RUPEE 76000.00 6666 WILDER 90000.00 --------------------- TOTAL SALARY 676000.00 2000 BLOOMER 15000.00 2000 HUTTON 44000.00 2000 JENSON 82000.00 2000 KIMBALL 45000.00 2000 KING 14500.00 - 1 -

DEPARTMENT/EMPLOYEE/EMPOSITION REPORT mm/dd/yy DEPT-ID-0410 EMP-LAST-NAME-0415 SALARY-AMOUNT-0420 2000 NICEMAN 14000 .00 --------------------- TOTAL SALARY 214500 .00 - 2 -

Compute Having

The HAVING clause is used to display the total salary of any department with more than 5 employees:

compute 'total salary' = sum(salary-amount-0420) group by dept-id-0410 having count > 5 ! display DEPARTMENT/EMPLOYEE/EMPOSITION REPORT mm/dd/yy DEPT-ID-0410 EMP-LAST-NAME-0415 SALARY-AMOUNT-0420 6666 HENDON 240000.00 6666 PAPAZEUS 100000.00 6666 PAPAZEUS 90000.00 6666 RUPEE 80000.00 6666 RUPEE 76000.00 6666 WILDER 90000.00 --------------------- TOTAL SALARY 676000.00 2000 BLOOMER 15000.00 2000 HUTTON 44000.00 2000 JENSON 82000.00 2000 KIMBALL 45000.00 2000 KING 14500.00 - 1 -

DEPARTMENT/EMPLOYEE/EMPOSITION REPORT mm/dd/yy DEPT-ID-0410 EMP-LAST-NAME-0415 SALARY-AMOUNT-0420 2000 NICEMAN 14000 .00 --------------------- TOTAL SALARY 214500 .00 3100 DOUGH 33000 .00 3100 GALLWAY 33000 .00 3100 GARFIELD 65000 .00 3100 GARFIELD 55000 .00 3100 GARFIELD 45000 .00 3100 GRANGER 34500 .00 3100 HEAROWITZ 33000 .00 3100 JACOBI 55000 .00 3100 JENSEN 37000 .00 3100 LINGER 42500 .00 - 2 -

DEPARTMENT/EMPLOYEE/EMPOSITION REPORT mm/dd/yy DEPT-ID-0410 EMP-LAST-NAME-0415 SALARY-AMOUNT-0420 3100 LINGER 38000.00 3100 LITERATA 37500.00 3100 TYRO 20000.00 --------------------- TOTAL SALARY 528500.00

For more information:

Global Syntax