What I am trying to do is to calculate the standard deviation within certain subsets of data divided by, State, Stratification, and GL Account (Level 5) Name. At the smallest grouping there is a cost/square foot associated with each building, but this cost may be repeated several times depending on how many vendors are in this building. I would like help modifying my query so that if the cost/square foot is identical, it will only be included once in the calculation for the standard deviation; essentially just selecting the unique values to be used in the calculation of standard deviation.
Here is what the query looks like now; if a building has several records the query is including that value multiple times in the calculation throwing it off. I have also included a function called zerotonull that selects only values greater than 0 for use in the calculation.
SELECT [2010PropertyOperational].State, [2010PropertyOperational].Stratification, [2010PropertyOperational].[GL Account (Level 5) Name], StDev(ZeroTonull([2010PropertyOperational]![Building Average 2009])) AS 2009StateSTDEV, StDev(zerotonull([2010PropertyOperational]![Building Average 2010])) AS 2010StateSTDEV, Avg(Zerotonull([2010PropertyOperational]![Building Average 2009])) AS 2009StateAVG, Avg(Zerotonull([2010PropertyOperational]![Building Average 2010])) AS 2010StateAVG INTO StateAVG
FROM 2010PropertyOperational
GROUP BY [2010PropertyOperational].State, [2010PropertyOperational].Stratification, [2010PropertyOperational].[GL Account (Level 5) Name];
Edit: I ended up just further grouping the data before calculating the averages. I'd still like to know how to do this without having to break up the data further if anyone would be willing to assist.
Bookmarks