MySQL VAR_SAMP() function

VAR_SAMP() function

MySQL VAR_SAMP() function returns the sample variance of a given expression. Variance is a measure of how spread out or dispersed a set of data points is.

This function is useful in -

• Variance is a key parameter in statistics that provides insights into the dispersion of data points around the mean.
• Variance can indicate how much your data points vary from the average. High variance might suggest inconsistencies or outliers in the data.
• In fields like finance, understanding the variance of returns on investments helps in assessing risk. A higher variance might indicate higher risk.
• In manufacturing or process control, variance can be used to monitor the consistency and quality of products.
• When dealing with scientific experiments or surveys, analyzing the variance of measurements can provide insights into the reliability of the results.

Syntax

VAR_SAMP(expr);

Where expr is an expression.

MySQL Version: 8.0

Example:

Sample table: purchase

Code:

-- This query calculates the sample variance of the 'total_cost' column in the 'purchase' table.
SELECT VAR_SAMP(total_cost)
-- This statement selects the sample variance of the 'total_cost' column.
FROM purchase;
-- This part of the query specifies the table from which data is being retrieved, which is 'purchase'.

Explanation

The following statement returns the sample variance of 'total_cost' from purchase table.

Explanation:

• The purpose of this SQL query is to compute the sample variance of the 'total_cost' values in the 'purchase' table.


• SELECT VAR_SAMP(total_cost): This part of the query selects the sample variance of the 'total_cost' column. Sample variance measures how much the values in the sample differ from the sample mean, and is used to estimate the variance of the population from which the sample is drawn.


• FROM purchase: This part specifies the table from which the data is being selected, which is the 'purchase' table.


• The query will return a single value, which is the sample variance of the 'total_cost' values in the 'purchase' table. This value provides insight into the degree of spread or dispersion of the 'total_cost' values around the mean for the sample represented by the 'purchase' table.

Output:

mysql> SELECT VAR_SAMP(total_cost)             
    -> FROM purchase;
+----------------------+
| VAR_SAMP(total_cost) |
+----------------------+
|        119366.666667 | 
+----------------------+
1 row in set (0.00 sec)

Example: MySQL VAR_SAMP() with where clause

The following statement returns the sample variance of 'expr' 'total_cost' from purchase table for the category 'cate_id' as given with WHERE clause.

Sample table: purchase

Code:

-- This query calculates the sample variance of the 'total_cost' column in the 'purchase' table for rows where the category ID is 'CA001'.
SELECT VAR_SAMP(total_cost)              
-- This statement selects the sample variance of the 'total_cost' column.
FROM purchase             
-- This part of the query specifies the table from which data is being retrieved, which is 'purchase'.
WHERE cate_id='CA001';
-- This clause filters the rows to include only those where the 'cate_id' column has the value 'CA001'.

Explanation:

• The purpose of this SQL query is to compute the sample variance of the 'total_cost' values for purchases belonging to the category 'CA001' in the 'purchase' table.


• SELECT VAR_SAMP(total_cost): This part of the query selects the sample variance of the 'total_cost' column. Sample variance measures how much the values in the sample differ from the sample mean, and is used to estimate the variance of the population from which the sample is drawn.


• FROM purchase: This part specifies the table from which the data is being selected, which is the 'purchase' table.


• WHERE cate_id='CA001': This clause filters the rows to include only those where the 'cate_id' column has the value 'CA001'. This ensures that the variance calculation is performed only on the subset of data that belongs to the specified category.

Output:

mysql> SELECT VAR_SAMP(total_cost)              
    -> FROM purchase             
    -> WHERE cate_id='CA001';
+----------------------+
| VAR_SAMP(total_cost) |
+----------------------+
|        236875.000000 | 
+----------------------+
1 row in set (0.00 sec)

Previous: VAR_POP()
Next: VARIANCE()