Filling Empty Values when Calculating Standard Deviation

Question

I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.

I have a table that contains every date over the past year and is updated moving forward. I use a left outer join on our sales table to come up with 4 columns:

Year | Month | Part ID | Value

The Year and Month columns come from the date table, and the Part ID and Value represent a summation from the sales table, where the summation is grouped based on Sales Year, Sales Month, and Part ID.

Now, I can calculate standard deviation fine using this.

A simple

SELECT
    PART_ID,
    STDEV(VALUE) STANDARD_DEV_VALUE
FROM QUERYX
GROUP BY PART_ID

This works fine when I have a sale for a given item each month, as it returns 12 months of data. But, if a part does not have a sale, I do not have data for that month.

For example, here is a possible output:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0

when what I need is:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0
2016 | 2     |   X  | 0
2016 | 3     |   X  | 0

 and so on upto a full 12 months... 

If I don't have this, I am not accurately calculating the standard deviation.

So, can someone help explain to me how I could fill in the Part ID when there are no sales? I have no link between the Part and the Dates, so I can only link on the sales dates. Thus, my issue.

This needs to be done in a single query, as there is other data being pulled here. Cursors are not an option. A temporary table is possible but it would have to execute very quickly.

Answer 1

I was able to resolve the issue through the use of a CROSS JOIN.

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.

I used this in a sub-query to generate a list of dates with every Part ID. This was joined to my other table, allowing me to substitute a value of 0 where null. The standard dev. then calculates correctly.

Answer 2

A hacky solution would be to create a CTE or table var containing all of the years / months that you need to analyze data for. Then do a left join of the four columns you're starting with against the years / months table so you'll have a null entry in the PartID and Value fields in months where a part wasn't sold. It seems like this was what you were attempting to do with the left join that you used to fetch these tables in the first place, but evidently something went wrong with that query, or you've omitted the records containing the null values.

Now use a CASE statement to replace the null fields with "0" in a subquery, and then calculate the STDEV of the value field in the subquery.

Let me know if you need any clarification, it's getting a bit late and I'm probably not explaining things very well.

Answer 3

An alternative is to calculate standard deviation using an equation instead of applying the built-in STDEV function to a padded result set. I've re-arranged the classic version of the equation as shown in the image below so that only one query is needed. (The classic version would require multiple sub-queries.) Assume @N = 12 (total number of months):

DECLARE @N SMALLINT = 12;

SELECT
  PART_ID,
  SQRT((SUM(SQUARE(VALUE))-SQUARE(SUM(VALUE))/@N)/(@N-1)) STANDARD_DEV_VALUE
FROM QUERYX
GROUP BY PART_ID

(If STDEVP is desired, replace @N-1 with @N)