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
Share your thoughts in the comments
Please Login to comment...