Aggregate functions

FairCom DB SQL provides a set of aggregative functions. These functions group together the values of multiple rows as input and perform calculations on these values. Each function returns a single value.

AVG

Syntax

AVG ( { [ALL] expression } | { DISTINCT column_ref } )

Description

The aggregate function AVG computes the average of a collection of values. The keyword DISTINCT specifies that the duplicate values are to be eliminated before computing the average.

• Null values are eliminated before the average value is computed. If all the values are null, the result is null.
• The argument to the function must be of type SMALLINT, INTEGER, NUMERIC, REAL or FLOAT.
• The result is of type NUMERIC.

Example

SELECT AVG (salary)

FROM employee

WHERE deptno = 20 ;

AVG windowing function

Syntax

AVG ( { [ALL] expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

The aggregate function AVG returns the average of the values in a group. column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument except that the result is of type INTEGER when the argument is of type SMALLINT or TINYINT. The result can have a null value.

Example

select empno, deptno, projno, sal, avg(sal) over (partition by deptno, projno order by projno) avg_proj_sal from emp;

EMPNO DEPTNO PROJNO SAL AVG_PROJ_SAL

----- ------ ------ --- -----------

7782 10 101 2450.00 2450.000000

7839 10 102 5000.00 3150.000000

7934 10 102 1300.00 3150.000000

7566 20 101 2975.00 2175.000000

7329 20 101 800.00 2175.000000

7876 20 101 1100.00 2175.000000

7788 20 101 3000.00 2175.000000

7902 20 101 3000.00 2175.000000

7698 30 101 2850.00 2850.000000

7844 30 102 1500.00 1225.000000

7900 30 102 950.00 1225.000000

7521 30 103 1250.00 1366.000000

7654 30 103 1250.00 1366.000000

7499 30 103 1600.00 1366.000000

See Also

• NTILE function (NTILE windowing function, NTILE Windowing Function)
• RANK function (RANK windowing function, RANK Windowing Function)
• ROW_NUMBER function (ROW_NUMBER windowing function, ROW_NUMBER Windowing Function)

COUNT

Syntax

COUNT ( { [ALL] expression } | { DISTINCT column_ref } | * )

Description

The aggregate function COUNT computes either the number of rows in a group of rows or the number of non-null values in a group of values.

• The keyword DISTINCT specifies that the duplicate values are to be eliminated before computing the count.
• If the argument to COUNT function is ‘*’, then the function computes the count of the number of rows in group.

For fixed length files, an exact row count is stored in the file header and can be immediately returned. For variable length files, an index is required that returns the number of keys. The first index that is found from the following is chosen: RECBYT, ROWID, first unique Index, first duplicate index. If no index is available, then a physical table scan is performed to count the actual number of rows in the table.

• If the argument to COUNT function is not ‘*’, then null values are eliminated before the number of rows is computed.
• The argument column_ref or expression can be of any type.
• The result of the function is of INTEGER type. The result is never null.

Example

SELECT COUNT (*)

FROM orders

WHERE order_date = SYSDATE ;

COUNT windowing function

Syntax

COUNT ( { [ALL] expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

The aggregate function COUNT returns the number of values in a group. The argument column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument. The result can have a null value.

Example

select empno, deptno, projno, first_value(empno) over (partition by deptno, projno order by sal) lowest_salary from emp;

EMPNO DEPTNO PROJNO EMP_PER_PROJ

----- ------ ------ -----------

7782 10 101 1

7839 10 102 2

7934 10 102 2

7566 20 101 5

7329 20 101 5

7876 20 101 5

7788 20 101 5

7902 20 101 5

7698 30 101 1

7844 30 102 2

7900 30 102 2

7521 30 103 3

7654 30 103 3

7499 30 103 3

See Also

• NTILE function (NTILE windowing function, NTILE Windowing Function)
• RANK function (RANK windowing function, RANK Windowing Function)
• ROW_NUMBER function (ROW_NUMBER windowing function, ROW_NUMBER Windowing Function)

COUNT_BIG

To resolve the problem of counting rows when they are more the 2^31-1, a new aggregate function COUNT_BIG has been added in V11.5 and later. This function is like COUNT except that it returns a BIGINT instead of an INTEGER.

COUNT_BIG

To resolve the problem of counting rows when they are more the 2^31-1, a new aggregate function COUNT_BIG has been added in V11.5 and later. This function is like COUNT except that it returns a BIGINT instead of an INTEGER.

MAX

Syntax

MAX ( { [ALL] expression } | { DISTINCT column_ref } )

Description

The aggregate function MAX returns the maximum value in a group of values.

• The specification of DISTINCT has no effect on the result.
• The result of the function is of the same data type as that of the argument.
• The argument column_ref or expression can be of any type.
• The result is null if the result set is empty or contains only null values.

Example

SELECT order_date, product, MAX (qty)

FROM orders

GROUP BY order_date, product ;

MIN

Syntax

MIN ( { [ALL] expression } | { DISTINCT column_ref } )

Description

The aggregate function MIN returns the minimum value in a group of values.

• The specification of DISTINCT has no effect on the result.
• The argument column_ref or expression can be of any type.
• The result of the function is of the same data type as that of the argument.
• The result is null if the result set is empty or contains only null values.

Example

SELECT MIN (salary)

FROM employee

WHERE deptno = 20 ;

STDEV windowing function

Declaration

STDEV ( { numeric_expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

The analytical window function STDEV returns the statistical standard deviation of all values within the specified window:

• The argument numeric_expression can be of any numeric type except BIT.

• The result is of type float.

• The result can have a null value.

Example

select c1, c3, stdev(c1) over (order by c3) from pc2;

C1 C3 STDEV(C1)

-- -- ---------

1 1 0.70710680000

2 1 0.70710680000

1 0.70710680000

4 2 1.82574200000

5 2 1.82574200000

3 1.82574200000

6 records selected

STDEVP windowing function

Declaration

STDEVP ( { numeric_expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

The analytical window function STDEVP returns the statistical standard deviation for the population for all values within the specified window.

• The argument numeric_expression can be of any numeric type except BIT.

• The result is of type float.

• Returns a value of 0 when the number of values in the population is 0 or 1.

Example

select c1, c3, stdevp(c1) over (order by c3) from pc2;

C1 C3 STDEVP(C1)

-- -- ----------

1 1 0.50000000000

2 1 0.50000000000

1 0.50000000000

4 2 1.58113900000

5 2 1.58113900000

3 1.58113900000

6 records selected

SUM

Syntax

SUM ( { [ALL] expression } | { DISTINCT column_ref } )

Description

The aggregate function SUM returns the sum of the values in a group. The keyword DISTINCT specifies that the duplicate values are to be eliminated before computing the sum.

• The argument column_ref or expression can be of any numeric type.
• The result of the function is of the same data type as that of the argument except that the result is of type INTEGER when the argument is of type SMALLINT or TINYINT.
• The result can have a null value.

Example

SELECT SUM (amount)

FROM orders

WHERE order_date = SYSDATE ;

SUM windowing function

Syntax

SUM ( { [ALL] expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

The aggregate function SUM returns the sum of the values in a group. The argument column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument except that the result is of type INTEGER when the argument is of type SMALLINT or TINYINT. The result can have a null value.

Example

select empno, deptno, projno, sal, sum(sal) over (partition by deptno, projno order by projno) project_sal from emp;

EMPNO DEPTNO PROJNO SAL PROJECT_SAL

----- ------ ------ --- -----------

7782 10 101 2450.00 2450.000000

7839 10 102 5000.00 6300.000000

7934 10 102 1300.00 6300.000000

7566 20 101 2975.00 10875.000000

7329 20 101 800.00 10875.000000

7876 20 101 1100.00 10875.000000

7788 20 101 3000.00 10875.000000

7902 20 101 3000.00 10875.000000

7698 30 101 2850.00 2850.000000

7844 30 102 1500.00 2450.000000

7900 30 102 950.00 2450.000000

7521 30 103 1250.00 4100.000000

7654 30 103 1250.00 4100.000000

See Also

• NTILE function (NTILE windowing function, NTILE Windowing Function)
• RANK function (RANK windowing function, RANK Windowing Function)
• ROW_NUMBER function (ROW_NUMBER windowing function, ROW_NUMBER Windowing Function)

VAR windowing function

Declaration

VAR ( { numeric_expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

VAR returns the statistical variance of all values in the specified expression.

• The argument numeric_expression can be of any numeric type except BIT. Aggregate functions and subqueries are not allowed.

• The result is of type float.

• The result can have a null value.

Example

select c1, c3, var(c1) over (order by c3) from pc2;

C1 C3 VAR(C1)

-- -- -------

1 1 0.50000000000

2 1 0.50000000000

1 0.50000000000

4 2 3.33333300000

5 2 3.33333300000

3 3.33333300000

6 records selected

See also

VARP

VARP windowing function

Declaration

VARP ( { expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

Requires partition_by_clause and/or order_by_clause.

Description

VARP returns the statistical variance for the population for all values in the specified expression. Use VARP when you have every data point for the population available. Otherwise, use the generic VAR for the sample.

• The argument numeric_expression can be of any numeric type except BIT. Aggregate functions and subqueries are not allowed.

• The result is of type float.

• Returns a value of 0 when the number of values in the population is 0 or 1.

Example

select c1, c3, varp(c1) over (order by c3) from pc2;

C1 C3 VARP(C1)

-- -- --------

1 1 0.25000000000

2 1 0.25000000000

1 0.25000000000

4 2 2.50000000000

5 2 2.50000000000

3 2.50000000000

6 records selected