STDDEV_SAMP() function in MySQL

STDDEV_SAMP() function in MySQL is used to calculate sample standard deviation of an expression. Syntax :

STDDEV_SAMP(expr);

Parameter : This method accepts only one parameter.

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

Returns : It returns the population standard deviation. Example-1 : Finding sample standard deviation of RunScored column from the given Player table using STDDEV_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 :

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 sample standard deviation for RunScored column.

SELECT  STDDEV_SAMP(RunScored ) as Samp_Standard_Deviation  
FROM Player ;

Output :

SAMP_STANDARD_DEVIATION
18.486932321687846

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

SELECT  STDDEV_SAMP(WicketsTaken) as Samp_Std_Dev_Wickets    
FROM Player ;

Output :

SAMP_STD_DEV_WICKETS
1.0488088481701516

Example-3 : In this example we are going to find the sample standard deviation of Income of Employee who are working in the company 'ABC Corp.' 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 who are working in 'ABC Corp.'

SELECT  'ABC Corp.' AS 'Company_Name',
STDDEV_SAMP(Annual_Income) as StdDevOfAnnualIncome  
FROM EmployeeDetails where WORKING_AT = 'ABC Corp.';

Output :

COMPANY_NAME	STDDEVOFANNUALINCOME
ABC Corp.	125830.57392117917