Open In App

STD() function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

With the help of STD() function we can calculate population Standard deviation of an expression in MySQL. But, if there are no matching rows in the given expression it returns Null.

Syntax :

STD(expr);

Parameter : This method accepts only one parameter.

  • expr : Input expression from which we want to calculate population standard deviation.

Returns : It returns the population standard deviation.

Example-1 :
Finding population standard deviation of RunScored column from the given Player table using STD 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 :
To verify used the following command as follows.

SELECT  * from Player ;

Output :

PLAYERID PLAYERNAME RUNSCORED WICKETSTAKEN
1 KL Rahul 52 0
2 Hardik Pandya 30 1
3 Ravindra Jadeja 18 2
4 Washington Sundar 10 1
5 D Chahar 11 2
6 Mitchell Starc 0 3

Now we are going to find population standard deviation for RunScored column.

SELECT  STD(RunScored ) as Pop_Standard_Deviation 
FROM Player ;

Output :

POP_STANDARD_DEVIATION
16.87618308609964

Example-2 :
Now we are going to find population standard deviation of WicketsTaken column.

SELECT  STD(WicketsTaken) as Pop_Std_Dev_Wickets   
FROM Player ;

Output :

POP_STD_DEV_WICKETS
0.9574271077563381

Example-3 :
In this example we are going to find the population standard deviation of Income of Employee who are working in the location ‘Kolkata’ 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_ID EMPLOYEE_NAME WORKING_AT WORK_LOCATION JOINING_DATE ANNUAL_INCOME
1 Amit Khan XYZ Digital Kolkata 2019-10-06 350000
2 Shreetama Pal ABC Corp. Kolkata 2018-12-16 500000
3 Aniket Sharma PQR Soln. Delhi 2020-01-11 300000
4 Maitree Jana XYZ Digital Kolkata 2019-05-01 400000
5 Priyanka Ojha ABC Corp. Delhi 2019-02-13 350000
6 Sayani Mitra XYZ Digital Kolkata 2019-09-15 320000
7 Nitin Dey PQR Soln. Delhi 2019-10-06 250000
8 Sujata Samanta PQR Soln. Kolkata 2020-10-06 350000
9 Sudip Majhi ABC Corp. Delhi 2018-10-30 600000
10 Sanjoy Kohli XYZ Digital Delhi 2019-04-18 450000

Now we are going to find population standard deviation of annual Income for those Employee whose work location is ‘Kolkata’

SELECT  'Kolkata' AS 'Work_Location',
STD(Annual_Income) as PopStdDevOfAnnualIncome  
FROM EmployeeDetails where Work_Location = 'Kolkata';

Output :

WORK_LOCATION POPSTDDEVOFANNUALINCOME
Kolkata 63435.006108614834

Last Updated : 11 Jan, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads