July 18, 2011 at 8:01 am
Hello everyone, I have the data in follwing format from my stored proc. I need to have standard deviation column filled in. when I use Stdev() function it throws in incorrect values.
for instance for the first 3 rows , i Need to have stdev as 1.52
next 3 rows - stdev 1
and last 3 rows -stdev 1
how do I do that. thank you !!
=============================================
id-Des -value -Avg - stdev- vendor -type - yr -month
-----------------------------------------------------------------
1 - P1 - 23 -24.3 - ___ - S - big - 2011 - june
2 - p2 - 24 -24.3 - ___ - S - big - 2011 - june
3 - p3 - 26 -24.3 - ___ - S - big - 2011 - june
------------------------------------------------------------------
1 - P1 - 10 -11 - ___ - M - small - 2011 - june
2 - p2 - 11 -11 - ___ - M - small - 2011 - june
3 - p3 - 12 -11- ___ - M - small - 2011 - june
-----------------------------------------------------------------
1 - P1 - 33 -34 - ___ - F - small - 2011 - may
2 - p2 - 34 -34 - ___ - F - small - 2011- may
3 - p3 - 35 -34- ___ - F - small - 2011 - may
-------------------------------------------------------
July 18, 2011 at 8:16 am
Can you post some ddl, sample data and desire results based on your sample data? Check out the link in my signature for best practices on posting ddl and data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2011 at 8:29 am
thanks for your reply. This is the sample data , I get from my stored procedure, I was wondering inserting this result set into table variable/temp table how can I convert this result set to have stdev values and still retain the same format and columns.
I would love to post everything, but because of policy , I am unable to do so. sorry about that.
July 18, 2011 at 8:38 am
Of course I understand sensitive information. The real point is that if you can send some ddl and sample data for your example I can spend my time working on code for you solution instead of creating tables and sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2011 at 10:54 am
This looks like it's down to a rounding that you're not expecting. The standard deviation of the first 3 rows is 1.527525232 and if you use a data type such as DECIMAL(9,2) this is correctly rounded up to 1.53.
If you want to ignore the rounding you can try this (but I wouldn't recommend it for accuracy's sake):
CREATE TABLE #table (id TINYINT, des CHAR(2), value DECIMAL(9,2), average DECIMAL(9,2), std_dev DECIMAL(18,9), vendor CHAR(1), vendor_type VARCHAR(5), yr SMALLINT, mth VARCHAR(4))
INSERT INTO #table VALUES (1, 'P1', 23, NULL, NULL, 'S', 'big', 2011, 'june')
INSERT INTO #table VALUES (2, 'P2', 24, NULL, NULL, 'S', 'big', 2011, 'june')
INSERT INTO #table VALUES (3, 'P3', 26, NULL, NULL, 'S', 'big', 2011, 'june')
INSERT INTO #table VALUES (1, 'P1', 10, NULL, NULL, 'M', 'small', 2011, 'june')
INSERT INTO #table VALUES (2, 'P2', 11, NULL, NULL, 'M', 'small', 2011, 'june')
INSERT INTO #table VALUES (3, 'P3', 12, NULL, NULL, 'M', 'small', 2011, 'june')
INSERT INTO #table VALUES (1, 'P1', 33, NULL, NULL, 'F', 'small', 2011, 'may')
INSERT INTO #table VALUES (2, 'P2', 34, NULL, NULL, 'F', 'small', 2011, 'may')
INSERT INTO #table VALUES (3, 'P3', 35, NULL, NULL, 'F', 'small', 2011, 'may')
-- Update Averages
UPDATE #table
SET average = (
SELECT AVG(value)
FROM #table avgTable
WHERE #table.vendor = avgTable.vendor
AND #table.vendor_type = avgTable.vendor_type
AND #table.yr = avgTable.yr
AND #table.mth = avgTable.mth
)
-- Update STDEV
UPDATE #table
SET std_dev = (
SELECT STDEV(value)
FROM #table avgTable
WHERE #table.vendor = avgTable.vendor
AND #table.vendor_type = avgTable.vendor_type
AND #table.yr = avgTable.yr
AND #table.mth = avgTable.mth
)
SELECT *
FROM #table
-- Round STDEV down (it's ugly AND mathematically incorrect!)
ALTER TABLE #table ADD std_dev_rounded FLOAT
UPDATE #table
SET std_dev_rounded = (
SELECT FLOOR(STDEV(value)*100.0) / 100
FROM #table avgTable
WHERE #table.vendor = avgTable.vendor
AND #table.vendor_type = avgTable.vendor_type
AND #table.yr = avgTable.yr
AND #table.mth = avgTable.mth
)
SELECT *
FROM #table
DROP TABLE #table
The multiply by 100, divide by 100 is important as FLOOR only works with integers. Using 100 will give you two decimal places.
Adrian Nichols
Many Thoughts in a Storm
www.adriannichols.com
July 18, 2011 at 2:28 pm
Thanks a trillion Adrian. that worked llike a charm. Exactly what I was looking for. Thank you soooooooooooooo much.
July 18, 2011 at 8:15 pm
If you are computing the standard deviation for the entire population of data -- and not a sample of the population -- then the formula is different. The function for a sample is STDEV(), the function for the entire population is STDEVP(). They will not return the same value, and it's up to you to know which one is appropriate for your data set.
Here's a link with info. on this: http://social.msdn.microsoft.com/Forums/br/sqlanalysisservices/thread/0d8edea2-f08e-41b7-b120-8ac08855c23b.
Not sure if this is relevant to your problem, as you didn't post any code....
Rich
July 21, 2011 at 1:23 pm
Thanks Rich for pointing that out. I used stdev() and that time I did not know about stdevp(). but after reading your reply, I looked at my data, and it looked fine to use stdev() .
That was a very nice idea and I will keep that in mind for future population vs sample stdev stuff.
thanks again !!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply