Distinctive Analytics

Statistical and mathematical functions in SQL allow you to perform calculations and analysis on numeric data within your database. These functions are invaluable for tasks ranging from basic statistical measures to more complex mathematical operations.

Sample Dataset (employees)

1. SUM()

Calculates the sum of a set of values.

SELECT SUM(salary) AS total_salary FROM employees;

This query calculates the total salary of all employees.

2. AVG()

Calculates the average (mean) of a set of values.

SELECT AVG(salary) AS avg_salary FROM employees;

This query calculates the average salary of employees.

3. MIN():

Returns the minimum value from a set of values.

Example:

SELECT MIN(salary) AS min_salary FROM employees;
48000

4. MAX():

Returns the maximum value from a set of values.

Example:

sql SELECT MAX(salary) AS max_salary FROM employees;
65000

This query finds the maximum salary among employees.

5. COUNT():

Counts the number of rows in a result set or the number of non-null values in a column.

Example:

SELECT COUNT(*) AS total_employees FROM employees;
7

This query counts the total number of employees.

6. STDDEV() and VARIANCE():

STDDEV() computes the standard deviation of a set of values, which is a measure of variation or dispersion.

VARIANCE() computes a set of values variance, which is the average of the squared differences from the mean.

Example:

SELECT STDDEV(salary) AS salary_stddev, VARIANCE(salary) AS salary_variance FROM employees;

The above calculations determine the variance and standard deviation of salaries.

7. CORR():

Calculates the correlation coefficient between two numeric columns. It measures the linear relationship between the two variables.

Example:

SELECT CORR(age, income) AS age_income_correlation FROM customers;

This query calculates the correlation between customer ages and their incomes.

8. COVAR_POP() and COVAR_SAMP()

COVAR_POP() calculates the population covariance between two numeric columns.

COVAR_SAMP() calculates the sample covariance between two numeric columns.

Example:

SELECT COVAR_POP(x, y) AS population_covariance, COVAR_SAMP(x, y) AS sample_covariance FROM data;

These queries calculate the population and sample covariances between columns x and y.

9. POWER() and SQRT()

POWER(x, y) raises x to the power of y.

SQRT(x) calculates the square root of x.

Example:

SELECT POWER(2, 3) AS two_cubed, SQRT(25) AS square_root_of_25;

These queries perform mathematical operations on numeric values.

These functions are fundamental for performing statistical analysis and mathematical calculations within SQL. They are essential for summarising data, detecting trends, and deriving meaningful insights from numerical data in your database.