MySQL provides a rich set of statistical functions that we can use to perform various statistical analyses directly within the database. These functions help us to derive insights and trends from large datasets and are essential for data analysis. This article will explore some of the key MySQL statistical functions.

What are Statistical Functions?

Statistical functions in MySQL are built-in functions that perform statistical analysis on numerical data within a database. These functions help us to summarize and understand data by calculating various statistical measures. Here are some statistical functions:

• AVG()
• SUM()
• COUNT()
• MIN()
• MAX()
• STDDEV()
• VARIANCE()

Demo Database

To explain the usage of each statistical function in MySQL, let’s create a sample table and populate it with sample data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    experience INT
);

INSERT INTO employees (id, name, department, salary, experience) VALUES
(1, 'Amit Sharma', 'Sales', 50000, 5),
(2, 'Anita Patel', 'HR', 60000, 7),
(3, 'Rajesh Kumar', 'IT', 70000, 10),
(4, 'Sita Verma', 'Sales', 55000, 6),
(5, 'Ravi Gupta', 'IT', 65000, 8),
(6, 'Neeta Singh', 'HR', 62000, 7),
(7, 'Vikram Rao', 'Sales', 58000, 5),
(8, 'Pooja Desai', 'IT', 72000, 12),
(9, 'Meena Reddy', 'HR', 61000, 9),
(10, 'Rohan Kapoor', 'Sales', 53000, 4);

Output:

employees table

1. AVG() – Average

MySQL AVG function calculates the average value of a numeric column.

Syntax

SELECT AVG(column_name) FROM table_name;

Example: In the below example we will find the average salary of employees.

SELECT AVG(salary) AS average_salary
FROM employees;

Output:

+----------------+
| average_salary  |
+----------------+
|   60600.000000  |
+----------------+

2. SUM() – Sum

The sum function in MySQL adds up all values in a numeric column.

Syntax

SELECT SUM(column_name) FROM table_name;

Example: In this example, we have find the total salary paid to all employees.

SELECT SUM(salary) AS total_salary
FROM employees;

Output:

+--------------+
| total_salary      |
+--------------+
|    606000.00     |
+--------------+

3. COUNT() – Count

The count function in MySQl is used to count the total number of rows or non-NULL values in a column.

Syntax

SELECT COUNT(column_name) FROM table_name;

Example: In this example, we will count the total number of employees.

SELECT COUNT(id) AS employee_count
FROM employees;

Output:

+----------------+
| employee_count |
+----------------+
|             10               |
+----------------+

4. MIN() – Minimum

The min function finds the smallest value in a numeric column.

Syntax

SELECT MIN(column_name) FROM table_name;

Example: To find the minimum salary among employees.

SELECT MIN(salary) AS min_salary
FROM employees;

Output:

+------------+
| min_salary   |
+------------+
|   50000.00    |
+------------+

5. MAX() – Maximum

The min function finds the maximum value in a numeric column.

Syntax

SELECT MAX(column_name) FROM table_name;

Example: To find the maximum salary among employees.

SELECT MAX(salary) AS max_salary
FROM employees;

Output:

+------------+
| max_salary  |
+------------+
|   72000.00     |
+------------+

6. STDDEV() – Standard Deviation

STDDEV function measures the amount of variation or dispersion of values.

Syntax

SELECT STDDEV(column_name) FROM table_name;

Example: In this example, we will calculate the standard deviation of salaries.

SELECT STDDEV(salary) AS stddev_salary
FROM employees;

Output:

+--------------------+
| stddev_salary             |
+--------------------+
| 6696.2676171132825   |
+--------------------+

7. VARIANCE() – Variance

VARIANCE function in MySQL measures how much values vary from the mean.

Syntax

SELECT VARIANCE(column_name) FROM table_name;

Example: To calculate the variance of salaries.

SELECT VARIANCE(salary) AS variance_salary
FROM employees;

Output:

+-----------------+
| variance_salary   |
+-----------------+
|        44840000       |
+-----------------+

Conclusion

MySQL statistical functions like AVG(), SUM(), COUNT(), MIN(), MAX(), STDDEV(), and VARIANCE() help us to perform data analysis directly in the database. Using these functions, we can quickly calculate averages, totals, counts, and other statistics to gain insights from your data.

FAQs on MySQL Statistical Functions

What is the difference between VARIANCE() and STDDEV()?

The VARIANCE() function measures the dispersion of values from the mean, while the STDDEV() function is the square root of the variance, providing a measure of dispersion in the same units as the data.

Can I use these functions with non-numeric data types?

No, statistical functions like AVG(), SUM(), STDDEV(), and VARIANCE() are designed to work with numeric data types only. However, COUNT(), MIN(), and MAX() can be used with other data types like dates and strings for counting rows or finding the minimum and maximum values.

How can I handle NULL values in my data when using these functions?

Most statistical functions in MySQL ignore NULL values. For example, AVG(), SUM(), and COUNT() will only consider non-NULL values in their calculations.