# Filling Empty Values when Calculating Standard Deviation

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.