Sql-server – Why is this Full Outer Join not working

join;sql serversql-server-2008

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?

Best Answer

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)