Summarizing Information › Summarizing Information About a Whole Table

Summarizing Information About a Whole Table

Summary lines This chapter tells you how to create reports containing summary lines, rather than detail lines. A detail line contains information about individual rows in a table. Summary lines contain summary information about detail lines. For example, a line containing salary information about Henrietta Hendon is a detail line. A line containing total salaries for all employees is a summary line.

Aggregate functions

To summarize information, CA OLQ provides the aggregate functions shown below. An aggregate function is a type of built-in function that evaluates all the values in a column and returns a single value.

How to use aggregate functions

You use aggregate functions in the list of column names or expressions following the SELECT statement. The function is calculated by giving the function (for example, SUM) followed by a column name or value in parentheses.

You can use all aggregate functions, except COUNT, with:

• Values in a column: sum(salary)
• An arithmetic expression: sum(salary * 0.06)
• Any combination of the items listed above

To count the number of selected rows, use COUNT followed by an asterisk in parentheses: count(*). CA OLQ displays the value in the report.

Example— Aggregate functions in column lists

List the number of employees within the company, and the company's total salary payment and average salary payment:

select count(*) as &xq.number of employees',
    sum(salary) as &xq.total salary',

    avg(salary) as &xq.average salary'

  from emp ! display

                                   EMP REPORT
                                    dd/mm/yy

       NUMBER OF EMPLOYEES          TOTAL SALARY           AVERAGE SALARY
       ───────────────────      ─────────────────────      ──────────────
                        19                 1070000.00            56315.78
     END OF REPORT