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.000000See Also
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 3See Also
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.000000See Also
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 selectedSee 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