Question: I have a table `Patients` which looks like this:

```
  PatientName    DateOftest    Eye     L1    L2    L3    L4     L5
  ----------------------------------------------------------------
    Mike         17-02-2009     L      23    25    40    32     30
    Mike         17-02-2009     R      25    30    34    35     24
    Bill         08-03-2006     L      20    24    30    24     25
    Bill         08-03-2006     R      18    25    27    30     24

```

Now my query below finds mean 

```
SELECT 
   PatientName, DateOfTest,    
   (MAX(L1) + MAX(L2) + MAX(L3) + MAX(L4) + MAX(L5))/4 as Mean, 
   SQRT(POW(L1 - Mean, 2) + POW(L2 - Mean, 2) + POW(L3 - Mean, 2) + POW(L4 - Mean, 2)  + POW(L5 - Mean, 2)) AS Standard Deviation, 
   'Binocular' Eye 
FROM 
   Patients 
GROUP BY  
   PatientName, DateOfTest;

```

The above query is wrong because I have not stored mean.. is there any way to store mean to find out standard deviation in my code.. I'm asking because I have very lengthy query and more records..

Answer: 
To store the mean and reuse it in your query, one option would be to use a [Common Table Expression](http://msdn.microsoft.com/en-us/library/ms190766%28SQL.105%29.aspx). You can join the CTE to the table to use the calculated mean multiple times.

I'll admit that didn't understand the following line...

```
SQRT(POW(L1-Mean,2)+POW(L2-Mean,2)+POW(L3-Mean,2)+POW(L4-Mean,2)+POW(L5-Mean,2))
as Standard Deviation, 'Binocular' Eye

```

...but the query below shows how you would integrate the calculated mean into that line, which I think might need some additional work as well.

```
--This is the CTE to calculate the mean
WITH Mean_CTE AS
(
  SELECT PatientName, DateOfTest,
    (MAX(L1) + MAX(L2) + MAX(L3) +  MAX(L4) + MAX(L5))/4 AS [Mean]
  FROM Patients
  GROUP BY PatientName, DateOfTest
)
--This is the original query
SELECT Patients.PatientName, Patients.DateOfTest, Mean_CTE.Mean AS Mean, 
  SQRT(POW(L1-Mean_CTE.Mean,2)+POW(L2-Mean_CTE.Mean,2)+POW(L3-Mean_CTE.Mean,2)
  +POW(L4-Mean_CTE.Mean,2)+POW(L5-Mean_CTE.Mean,2)) as Standard Deviation, 
  'Binocular' Eye
FROM Patients
INNER JOIN Mean_CTE --This is where you join the two
ON Patients.PatientName = Mean_CTE.PatientName
    AND Patients.DateOfTest = Mean_CTE.DateOfTest
GROUP BY Patients.PatientName, Patients.DateOfTest, Mean_CTE.Mean;

```
What about the possibility of adding a CALCULATED column to the table that stores the result of the formula??

This is a rather simple concept, and will sotre the value for the formula.

[http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx](http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx)