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.
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.