{"id":10891,"date":"2023-10-09T18:39:26","date_gmt":"2023-10-10T01:39:26","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=10891"},"modified":"2023-11-09T17:58:36","modified_gmt":"2023-11-10T00:58:36","slug":"mysql-var_samp","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/mysql-var_samp\/","title":{"rendered":"MySQL VAR_SAMP() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL VAR_SAMP()<\/code> function to calculate the sample variance of values in a column of a table.<\/p>\n\n\n\n

Introduction to the MySQL VAR_SAMP() function<\/h2>\n\n\n\n

The VAR_SAMP()<\/code> function is an aggregate function<\/a> that returns the sample variance<\/strong> of values in a column of a table. <\/p>\n\n\n\n

Here’s the syntax of the VAR_SAMP()<\/code> function:<\/p>\n\n\n

SELECT<\/span> VAR_SAMP<\/span>(column_name)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The VAR_SAMP()<\/code> returns NULL<\/code> if the values are NULL<\/code> or there are no matching rows. <\/p>\n\n\n\n
In practice, you use the VAR_SAMP()<\/code> function when you have a sample from the population, not the entire population.<\/p>\n\n\n\n
MySQL VAR_SAMP() function example<\/h2>\n\n\n\n
First, create a new table<\/a> called apples<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> apples(\n   id<\/span> INT<\/span> AUTO_INCREMENT,\n   color VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n   weight DECIMAL<\/span>(6<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\n   PRIMARY KEY<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> apples (color, weight) VALUES<\/span>\n    ('Red'<\/span>, 0.6<\/span>),\n    ('Green'<\/span>, 0.4<\/span>),\n    ('Yellow'<\/span>, 0.35<\/span>),\n    ('Red'<\/span>, 0.28<\/span>),\n    ('Green'<\/span>, 0.42<\/span>),\n    ('Orange'<\/span>, 0.38<\/span>),\n    ('Red'<\/span>, 0.31<\/span>),\n    ('Purple'<\/span>, 0.45<\/span>),\n    ('Green'<\/span>, 0.37<\/span>),\n    ('Yellow'<\/span>, 0.33<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the apples<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> apples;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+--------+--------+<\/span>\n| id | color  | weight |\n+----+--------+--------+<\/span>\n|  1 | Red    |   0.60 |\n|  2 | Green  |   0.40 |\n|  3 | Yellow |   0.35 |\n|  4 | Red    |   0.28 |\n|  5 | Green  |   0.42 |\n|  6 | Orange |   0.38 |\n|  7 | Red    |   0.31 |\n|  8 | Purple |   0.45 |\n|  9 | Green  |   0.37 |\n| 10 | Yellow |   0.33 |\n+----+--------+--------+<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, calculate the sample variance of the weight<\/code> of the apples<\/code> table:<\/p>\n\n\n
SELECT<\/span> color, VAR_SAMP<\/span>(weight)\nFROM<\/span> apples\nGROUP<\/span> BY<\/span> color;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+------------------------+<\/span>\n| color  | VAR_SAMP(weight)       |\n+--------+------------------------+<\/span>\n| Red    |   0.031233333333333325 |\n| Green  |  0.0006333333333333332 |\n| Yellow | 0.00019999999999999868 |\n| Orange |                   NULL |\n| Purple |                   NULL |\n+--------+------------------------+<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL VAR_SAMP()<\/code> function to calculate the sample variance of values in a column of a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert some rows<\/a> into the apples<\/code> table:<\/p>\n\n\n
Note that to calculate the population variance, you use the VAR_POP()<\/a><\/code> or VARIANCE()<\/a><\/code> function.<\/p>\n\n\n\n