March 7, 2012 at 9:03 am
I have a report that is in production. Now, I have been asked to add a column to show Standard Deviation of values in 6 columns. Is it doable. Can I add a calculated field within the dataset to represent that value and then use it in a column?
Thanks.
kr
March 7, 2012 at 11:10 am
the SQL server function STDEV takes a column name;
it sounds like you want the STDEV of 6 columns in a specific row, or a
all values ?
if it's the 6 columns per row, you need an unpivot query to get your columns to rows.
if it's all values, it's a union.(select id,colA UNION ALL select id,colM etc)
then you have to join them as a subquery to your original query to get the new column.
if you can provide sample data and DDL of the tables involved, we could suggest a solution.
Lowell
March 7, 2012 at 11:44 am
Here is part of the report:
Jul 11Aug 11Sep 11Oct 11Nov 11Dec 11TotalStd Dev
11-62137404414719.50
-4-10000-51.60
000000.00
888888480.00
00-5000-52.04
The report doesn't have Std Dev column. I exported report to Excel and added Std Dev column. The formula in Excel is =STDEV.S(E6:J6) Where [Jul 11] is E6. I hope this explains the problem I am trying to solve.
There are two more cols on the left, Dept and Project which together identify the row.
March 7, 2012 at 12:17 pm
conceptually, i understand now; it'd a STDEV of the 6 values, right?
if you can provide a set of commands for some sample data
CREATE TABLE ...
and also
INSERT INTO...
we could show you how toi unpivot your data; as it is now, I'd spend a lot of time converting your excel paste into what I think is the right datatypes, formatting it, etc.
if you can do that, volunteers here can provide you with a tested, working solution, instead of some guesses and vague advice.
SELECT
YOURTABLE.* ,
MyAlias.StandardDeviation
FROM YOURTABLE
LEFT OUTER JOIN
(SELECT STDEV(PIVOTEDCOLUMN) AS StandardDeviation
FROM (SELECT [Code],[AttributeCode],[AttributeValue]
FROM (SELECT
[ID],
[Col1],[Col2],[Col3],[Col4],[Col5],[Col6]
FROM YOURTABLE) p
UNPIVOT
([AttributeValue] FOR [AttributeCode] IN
([Col1],[Col2],[Col3],[Col4],[Col5],[Col6]) )AS unpvt
)unpv
) MyAlias
ON YOURTABLE.ID = MyAlias.ID
Lowell
March 7, 2012 at 1:01 pm
Thanks. I think I know how it should be done but it would require me to go through some trial and error (this pivot/ unpivot command is confusing for me.). However, to save some time (I am being lazy) here is further detail.
The data is being returned by a stored procedure. At the end of the procedure I do a simple select on a table whose schema is like this:
Dept int, Project varchar(25), ProjectDescription varchar(50), Column1 int, Column2 int, Column3 int, Column4 int, Column5 int, Column6 int, Total int.
Column1 through Column6 are the values that I need to calculate StDev on.
I am using these generaic column names becaue, in the report, I need to show the month & year that each column data belogs to. Since this 6 month period is not fixed, I generate report column headings based on the @startdate parameter.
March 7, 2012 at 4:28 pm
With your help, I was able to figure out the final query. Here it is for your review.
SELECTv.Dept, v.Project, ProjDesc, Period01, Period02, Period03,
Period04, Period05, Period06, Total , ROUND(m.StdDev, 2) StdDev
FROM#Variance v LEFT OUTER JOIN
(SELECTProject, Dept, StDev(Diff) StdDev
FROM(SELECTProject, Dept, Col, Diff
FROM(SELECTDept, Project, Period01, Period02, Period03, Period04, Period05, Period06
FROM#Variance
) p
UNPIVOT(Diff FOR Col IN (Period01, Period02, Period03, Period04, Period05, Period06)
) u
) pv
GROUPBY
Project, Dept
) m ON v.Project = m.Project AND v.Dept = m.Dept
ORDERBY
v.Dept, v.Project
Thanks for all the help.
kr
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply