How to get statistical data from a column in standard SQL. In particular it shows how to get the minimum, maximum, average values, and the standard deviation.
Counting values
There are several ways of counting values, so this topic has a dedicated article: Count a table rows.
Finding the minimum value
Example: Get the minimum salary in the company.
SELECT min(salary) FROM employees;Finding the maximum value
Example: Get the maximum salary in the company.
SELECT max(salary) FROM employees;Obtaining the average and the standard deviation
Example: Get the average salary in the company.
SELECT avg(salary), stddev(salary) FROM employees;Obtaining the sum of a column
NOTE: This only works with numeric columns.
Example: Get the sum of the salaries of all the company’s employees.
SELECT sum(salary) FROM employees;Aggregating unique values
Some of these functions can aggregate unique values, meaning that identical values will be counted once.
For example, if we use AVG(DISTINCT n), we will get the same result for these series of values:
- 1, 2, 3
- 1, 2, 3, 3, 3
SQLite
SQLite supports sum(), as shown above, but it always returns an integer number – even when summing float numbers.
It also supports a total() function, which always returns a float, so it is meant to sum float numbers.
Reference
Related articles:
SQLite documentation: