Functions › Aggregate-function › Expansion of Aggregate-function

Expansion of Aggregate-function

The expanded parameters of aggregate-function represent an aggregate function in an SQL statement.

Syntax

Expansion of aggregate-function

►►─┬─┬─ AVG ─┬─ ( ─┬─┬────────────┬─┬───────────────┬─── column-name ──┬─ )─┬─►◄
   │ ├─ MAX ─┤     │ └─ DISTINCT ─┘ ├─ table-name. ─┤                  │    │
   │ ├─ MIN ─┤     │                └─ alias. ──────┘                  │    │
   │ └─ SUM ─┘     └─ all value-expression ────────────────────────────┘    │
   └─ COUNT ( ─┬─ * ───────────────────────────────────────────────┬─ ) ────┘
               └─┬────────────┬─┬───────────────┬─── column-name ──┘
                 └─ DISTINCT ─┘ ├─ table-name. ─┤
                                └─ alias. ──────┘

Parameters

AVG

Computes the arithmetic mean of the non-null values specified by the argument.

If no rows are found for the function, or if all rows found contain null values, the result of the function is null.

MAX

Finds the largest of the non-null values specified by the argument.

If no rows are found for the function, or if all rows found contain null values, the result of the function is null.

MIN

Finds the smallest of the non-null values specified by the argument.

If no rows are found for the function, or if all rows found contain null values, the result of the function is null.

SUM

Computes the total of the non-null values specified by the argument.

If no rows are found for the function, or if all rows found contain null values, the result of the function is null.

DISTINCT

Directs CA IDMS to exclude both duplicate values and null values from the set of values identified by column-name before evaluating the function.

If you do not specify DISTINCT, CA IDMS excludes only null values.

column-name

Specifies the set of values in the named column. CA IDMS excludes null values from the set before evaluating the function.

For AVG and SUM, the named column must have an approximate or exact numeric data type.

table-name

Specifies the table, view, procedure or table procedure that includes the named column. For expanded table-name syntax, see Expansion of Table-name.

alias

Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. The alias must be defined in the FROM parameter of the subquery, query specification, or SELECT statement that includes the aggregate function.

all value-expression

Specifies the set of values derived from the evaluation of a value expression. Null values are excluded from the set before the function is evaluated.

When used as the argument of an aggregate function, value-expression:

• Must include at least one column reference
• Cannot include another aggregate function (that is, you cannot nest aggregate functions)
• Cannot include both arithmetic operators and outer references

  Note: For more information about outer references, see Subqueries.

For AVG and SUM, the values in the set must have an approximate or exact numeric data type.

The keyword ALL is optional and does not affect the evaluation of the function. For expanded value-expression syntax, see Expansion of Value-expression.

COUNT

Counts the number of rows where the column identified by the argument contains non-null values.

If no rows are found for the function, or if all rows found contain null values, the result of the function is 0.

*

Counts the rows in the requested grouping, if any, of the result table.

Usage

The DISTINCT and ALL Parameters with MAX and MIN

When the argument of the MAX or MIN function is a single column, the result of the function is the same whether you use the DISTINCT parameter. However, CA IDMS evaluates the function more efficiently when you omit DISTINCT.

Aggregate Functions with Grouped Tables

When used in a subquery, query-specification, or SELECT statement that includes the GROUP BY parameter, aggregate functions are evaluated once for each group in the table.

If there is no GROUP BY parameter, aggregate functions are evaluated once for the entire result table.

Data Types of Function Results

Note: For more information about data type conversion, see Comparison, Assignment, Arithmetic, and Concatenation Operations.

Examples

Finding an Average

The following SELECT statement returns the average number of days employees took as vacation time or sick time in the 1989 fiscal year:

select avg(vac_taken + sick_taken)
   from benefits
   where fiscal_year = '89';

Counting Rows that Satisfy a Condition

The following SELECT statement counts the insurance plans not currently used by any employees:

select count(*)
   from insurance_plan
   where plan_code not in
      (select plan_code
         from coverage);

Counting Rows in Table Groupings

The following SELECT statement counts the number of different jobs in each department:

select d.dept_id, d.dept_name, count(distinct p.job_id)
   from department d, employee e, position p
   where d.dept_id = e.dept_id
      and e.emp_id = p.emp_id
   group by d.dept_id, d.dept_name;

Selecting the Largest Value

The following SELECT statement identifies the jobs that have the largest number of positions open:

select job_id, job_title, num_open
   from job
   where num_open =
      (select max(num_open)
         from job);

More Information

• For more information about subqueries and query specifications, see Query Specifications, Subqueries, Query Expressions, and Cursor Specifications.
• For more information about the SELECT statement, see SELECT.

More Information:

Query Specifications, Subqueries, Query Expressions, and Cursor Specifications

SELECT