ISNULL() for calculated column in Full Join. SQL Server

Question

I'm currently facing a problem when calculating the standard deviation in an SQL Server statement.

My problem: I have two tables.

T1:

Reg_Month
---------   
1
2
3
...

T2:

Product  Month   Consumption
-------------------------------
ProdA      1       200
ProdB      1        10
ProdA      1       300
ProdC      2       100
ProdA      2       200
...

Now what I want is something like this, for calculating my STDEV over a year:

Reg_Month   Product   Month   Sum
---------------------------------
   1        ProdA       1     500
   1        ProdB       1      10
   1        ProdC       1       0
   2        ProdA       2     200
   2        ProdB       2       0
   2        ProdC       2       0

So now I don't need this table to be set up, but I need to calculate the STDEV and AVG of the column "Sum" for each Product. The Problem is to include the NULLS.

This gives me the table I want for ProdA, but with NULLS whenever there was no consumption:

SELECT * 
FROM T1
FULL JOIN (SELECT Product, Month, SUM(Consumption) AS Sum, 
           FROM T2
           WHERE (Product = 'ProdA')
           GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)`

This gives me the STDEV:

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, SUM(Consumption) AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'`

But the problem is, that it doesn't give me the correct STDEV for the entire year, if there is a month in which there was no consumption, because the NULLs (that appear due to the join) are ignored.

My approaches:

ISNULL():

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, ISNULL(SUM(Consumption), 0) AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'`

doesn't work (maybe because the null is generated after the join?)

CASE:

SELECT Stdev(Sum) 
FROM 
    (SELECT * 
     FROM T1
     FULL JOIN (SELECT Product, Month, CASE WHEN SUM(Consumption) IS NULL THEN 0 ELSE Sum(Consumption) END AS Sum, 
                FROM T2
                WHERE (Product = 'ProdA')
                GROUP BY Month, Product) sub ON (T1.Reg_Month = T2.Month)) sub 
WHERE Product = 'ProdA'

doesn't work (probably the same reason)

I hope I was able to illustrate my example properly. Now, do you have any idea how get the right results for the STDEV?

Your input would be greatly appreciated!

Thanks a lot,

Clemens

Answer 1

if you are only doing one product at a time

SELECT sum(isnull(T2month.MonthSum ,0))
     , Stdev(isnull(T2month.MonthSum ,0))
  FROM T1
  LEFT JOIN
       (select Month, sum(Consumption) as MonthSum 
          from T2
         where Product = 'ProdA' 
         group by Month) T2month 
    ON T1.Reg_Month = T2month.Month

for all products

SELECT Product.Name  
     , sum(isnull(T2month.MonthSum ,0))
     , Stdev(isnull(T2month.MonthSum ,0))
  FROM T1 
  cross apply  
       (values ('ProdA'), ('ProdB'), ('ProdC')) Product(Name)
  LEFT JOIN
       (select Product, Month, sum(Consumption) as MonthSum 
          from T2
         group by Product, Month) T2month 
    ON T1.Reg_Month = T2month.Month 
   AND T2month.Product = Product.Name 
 Group By Product.Name

Hey OP left join does NOT leave out periods
That is what a left join does

select lj.val, isnull(jj.val,0)
  from ( values (1), (2), (3), (4) ) lj(val) 
  left join ( values (1), (2), (4) ) jj(val) 
    on lj.val = jj.val 
 order by lj.val