Why is this Full Outer Join not working?

I’ve used Full Outer Joins before to get my desired results, but maybe I don’t fully understand the concept because I am not able to accomplish what should be a simple join.

I have 2 tables (which I’l call t1 and t2) with 2 fields each:

t1

Policy_Number Premium
101             15
102              7
103             10
108             25
111              3

t2

Policy_Number   Loss
101              5
103              9
107              20

What I am trying to do is to get the sum of Premium and Sum of Losses from both tables and also the Policy_Number. The code I am using is:

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, t1.policynumber
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by t1.policynumber

The above code will return the correct sum totals but it will group all records where there isn’t a policy_number match under “NULL” policy_number.

I would like my result to look like this

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

etc…..

I do not want a result that shows a NULL policy_number as shown below (since there is no such thing as a NULL policy_number. This is just the total for when the policy_number from both tables don’t match):

Policy_Number    Prem_Sum   Loss_Sum
   NULL            35         NULL

If I Select and group by t2.policy_number instead of t1.policy_number then I get something like below as a record.

Policy_Number    Prem_Sum   Loss_Sum
   NULL            NULL         20

Again, I don’t mind seeing NULL under Prem_Sum or under Loss_sum but I don’t want a NULL under Policy_Number. I would like my results to be something like

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

ect…..

I thought the full outer join would accomplish this but I guess I am missing something. I was thinking maybe I could select and group by both t1.policy_number and t2.policy_number as a sub query and then maybe do a CASE in the outer query or something??? I don’t think it should be this complicated.

Any ideas or advice?

You should do an isnull on both policynumbers so that you can group properly.

Since it’s an outer-join, there’s the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)

The full outer join will create the record structure that you need, but it won’t put policy number 107 into Table 1 for you.

I think what you need is something along the lines of

select coalesce(t1.policy_number, t2.policy_number) as PolicyNumber, 
sum(t1.premium) as PremSum, sum(t2.loss) as LossSum
from t1 full outer join t2 on t1.policy_number = t2.policy_number
group by coalesce(t1.policy_number, t2.policy_number)

To provide a little more information as to why your specific query didn’t work. Your starting code was:

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, t1.policynumber 
from t1 full outer join t2 on t1.policynumber = t2.policynumber 
group by t1.policynumber 

At first glance, this looks like it should work. However, notice that the third column specified is t1.policynumber. This is also the sole grouping column. Because of this SQL Server only sees the values in t1, leaving any values not in t1 as a null (because, remember, this is a full outer join). The isnull(t1.policynumber,t2.policynumber) code will provide you with all non-null values in t1, then use values in t2.