Aggregative Functions

SUM() function computes the sum of all field values. Therefore the field should have the numerical data-type. The result returning by this function has the same data-type as the field, but the result accuracy can be higher.

What are the total plan and total real sales volume in the company?

SELECT SUM(quota), SUM(sales)
FROM salesreps 

AVG function computes the average of all field values. The field should have the numerical type. As AVG function first sums up all values contained in the field and then divide the sum on the number of these values, the result has double type.

Compute the average goods' price from ACI producer.

SELECT AVG (price)
FROM products
WHERE mfr_id = 'ACI' 

MIN() and MAX() functions allow to find correspondingly the less and the most values in the field.

The field can contain numerical or string values or date/time values. Result has the same data-type as the field itself.

SELECT MIN(quota), MAX(quota)
FROM salesreps

Comparison of numbers, records, dates, time intervals occurs according to the standard rules.

COUNT(*) function counts the number of records in the resulting table.

Data-type of the field can be of any kind. This function always returns the fixed-point number irrespective of data-type of the field.

How many clients are there in the company?

SELECT COUNT(*)
FROM customers

COUNT(expr) function counts the value number in the field.

How many clients are there in the company?

SELECT COUNT(cust_num)
FROM customers

BIT_AND( expr ) Calculates bitwise AND for values of group. Returns value with all bits 1 if there is no records in the group.

BIT_OR( expr ) Calculates bitwise OR for values of group. Returns zero (0) if there is no records in the group.

BIT_XOR( expr ) Calculates bitwise OR for values of group. Returns zero (0) if there is no records in the group.

STDDEV(expr) function returns sample standard deviation of expr, a set of numbers. STDDEV_POP(expr) function calculates population standard deviation and returns the square root of the population variance.

Data-type of the expression must be numeric.

SELECT STDDEV(amount), STDDEV_POP(amount) FROM Mortgage

COVAR(expr1, expr2) function returns the population covariance of a set of number pairs.

Data-type of both expressions must be numeric. Valentina applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Valentina makes the following computation:

(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n

where n is the number of (expr1, expr2) pairs where neither expr1 nor expr2 is null.

The function returns a value of type DOUBLE. If the function is applied to an empty set, then it returns null.

SELECT COVAR(list_price, min_price) FROM ProductInfo

CORR(expr1, expr2) function returns the coefficient of correlation of a set of number pairs

Data-type of both expressions must be numeric. Valentina applies the function to the set of (expr1, expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Valentina makes the following computation:

COVAR(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))

where n is the number of (expr1, expr2) pairs where neither expr1 nor expr2 is null.

The function returns a value of type DOUBLE. If the function is applied to an empty set, then it returns null.

SELECT CORR(list_price, min_price) FROM ProductInfo

GROUP_CONCAT(expr1 [ORDER_BY_CLAUSE] [SEPARATOR',']) function returns a string result with the concatenated non-NULL, optionally sorted by ORDER_BY_CLAUSE criteria, values from a group. It returns NULL if there are no non-NULL values.

SELECT GROUP_CONCAT(Name) FROM Person
SELECT GROUP_CONCAT(Name ORDER BY id DESC) FROM Person
SELECT GROUP_CONCAT(Name SEPARATOR '') FROM Person

FIRST(expr1 [ORDER_BY_CLAUSE]) function returns a first value (optionally sorted by ORDER_BY_CLAUSE criteria) in the group. LAST(expr1 [ORDER_BY_CLAUSE]) function returns a last value (optionally sorted by ORDER_BY_CLAUSE criteria) in the group.

SELECT FIRST(Name) FROM Person
SELECT LAST(Name) FROM Person
 
SELECT FIRST(Name ORDER BY priority) FROM Person
SELECT LAST(Name ORDER BY priority) FROM Person