# 0210 Aggregate Functions and Logical Functions

## Aggregation Functions

### Sys.Functions System Table

We can get a list of aggregate functions from the system table sys.functions. Aggregate functions are of the type 3.

``SELECT DISTINCT name, mod FROM sys.functions WHERE type = 3 ORDER BY name;   ``

We can divide aggregate functions into three groups:

### Statistical Functions

Numbers, from Number column, can be divided into smaller and larger numbers. Half of the numbers will be smaller and the other half will be larger numbers. The number on the border between the smaller and larger numbers is the median.

Median is a special case of a quantile. Median is a 50% quantile. But we can differently divide our numbers. We can divide them 60% vs 40%, so that 60% numbers are on the smaller side, and 40% is on the bigger side. Number between them would be called 60% quantile. In our example below, "60 % quantile" is 2.8, which means that 60% of numbers is below 2.8. This would be more obvious if we had more numbers in our column.

Variance and standard deviation are calculated differently depending whether our data represent a population or a sample.

Covariance in statistics measures the extent to which two variables vary linearly.  Correlation is just covariance measured in normalized units. Unfortunately, there is a bug in MonetDB, version 11.49.09, and all of these functions will return wrong results.

All statistic function will ignore NULLs.

## Logical Operators

### Unary Operators

All other logical operators will return Null if at least one of its arguments is Null.

Most SQL functions will either return NULL if one of the arguments is NULL, or will ignore rows with NULL values.

### Logical Functions

Operators AND, OR and NOT have alternative syntax where they work like functions.  XOR can not work like operator, only like a function.