Skip to content
Related Articles

Related Articles

Save Article
Improve Article
Save Article
Like Article

VAR_SAMP() function in MySQL

  • Difficulty Level : Basic
  • Last Updated : 30 Dec, 2020

VAR_SAMP() function in MySQL is used to calculate sample variance of an expression.

Syntax :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

VAR_SAMP(expr);

Parameter : This method accepts only one parameter.

  • expr : Input expression from which we want to calculate sample variance.

Returns : It returns the sample variance.



Example-1 :
Finding sample variance of RunScored column from the given Player table using VAR_SAMP Function.

Creating a Player table :

CREATE TABLE Player 
(
PlayerId INT AUTO_INCREMENT,  
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL,
PRIMARY KEY(PlayerId)
);

Inserting data into the Table :

INSERT INTO Player 
(PlayerName, RunScored,  WicketsTaken )
VALUES
('
KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

To verify used the following command as follows.

SELECT  * from Player ;

Output :

PLAYERIDPLAYERNAMERUNSCOREDWICKETSTAKEN
1KL Rahul520
2Hardik Pandya301
3Ravindra Jadeja182
4Washington Sundar101
5D Chahar112
6Mitchell Starc03

Now we are going to find sample variance for RunScored column.

SELECT  VAR_SAMP(RunScored ) as Run_Variance 
FROM Player ;

Output :

RUN_VARIANCE
341.7666666666667

Example-2 :
Now we are going to find sample variance of WicketsTaken column.



SELECT  VAR_SAMP(WicketsTaken) as Wicket_Variance 
FROM Player ;

Output :

WICKET_VARIANCE
1.1

Example-3 :
In this example we are going to find the sample variance of Income of Employee who are working in the company ‘PQR Soln.’ To demonstrate create a table named EmloyeeDetails.

CREATE TABLE EmployeeDetails(

Employee_Id INT AUTO_INCREMENT,  
Employee_Name VARCHAR(100) NOT NULL,
Working_At VARCHAR(20) NOT NULL,
Work_Location  VARCHAR(20) NOT NULL,
Joining_Date DATE NOT NULL,
Annual_Income INT  NOT NULL,
PRIMARY KEY(Employee_Id )
);

Inserting data into the Table :

INSERT INTO  
EmployeeDetails(Employee_Name, Working_At, Work_Location, Joining_Date, Annual_Income )

VALUES
('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000 ),
('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000 ),
('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000 ),
('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000 ),
('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000 ),
('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000 ),
('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000 ),
('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000 ),
('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000 ),
('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000 ) ;

To verify used the following command as follows.

Select * FROM EmployeeDetails;

Output :

EMPLOYEE_IDEMPLOYEE_NAMEWORKING_ATWORK_LOCATIONJOINING_DATEANNUAL_INCOME
1Amit KhanXYZ DigitalKolkata2019-10-06350000
2Shreetama PalABC Corp.Kolkata2018-12-16500000
3Aniket SharmaPQR Soln.Delhi2020-01-11300000
4Maitree JanaXYZ DigitalKolkata2019-05-01400000
5Priyanka OjhaABC Corp.Delhi2019-02-13350000
6Sayani MitraXYZ DigitalKolkata2019-09-15320000
7Nitin DeyPQR Soln.Delhi2019-10-06250000
8Sujata SamantaPQR Soln.Kolkata2020-10-06350000
9Sudip MajhiABC Corp.Delhi2018-10-30600000
10Sanjoy KohliXYZ DigitalDelhi2019-04-18450000

Now we are going to find sample variance of annual Income for those Employee who are working in ‘PQR Soln.’

SELECT 'PQR Soln.' AS 'Company_Name',
VAR_SAMP(Annual_Income) as VarianceOfAnnualIncome  
FROM EmployeeDetails where WORKING_AT = 'PQR Soln.';

Output :

COMPANY_NAMEVARIANCEOFANNUALINCOME
PQR Soln.2500000000
My Personal Notes arrow_drop_up
Recommended Articles
Page :