JOIN changes values to null?-CodePudding

I'm trying to join two tables that have this type of format:

Table 1:
Col1:         Col2:     Col3:     col4, col5, col6...
2015-01-01    b         3        
2015-01-01    <null>    5 
2015-01-01    b         5          
2015-01-01    i         6        
2015-01-02    b         4        
2015-01-02    i         5    
2015-01-02    <null>    5  
2015-01-02    i         2

And some other columns in the 1st table. The second table only has the sum of col3 grouped by Col2, thanks to the query:

select Col1, Col2, sum(Col3)
from Table1
group by Col1, Col2
order by Col1 asc;

So the table 2 looks like this:

Table 2:
Col1:           Col2:     sum_Col3:    
2015-01-01      b         8        
2015-01-01      i         6         
2015-01-02      b         4        
2015-01-02      i         7

I would simply like to join the column sum_Col3 to table1 yet, because of the null values on Col2, it's giving me somethin odd.

Ideally, the table would look like this:

Table 1:
Col1:         Col2:     Col3:     sum_Col3:   col4, col5, col6...
2015-01-01    b         3         8
2015-01-01    <null>    5         <null>
2015-01-01    b         5         8 
2015-01-01    i         6         6
2015-01-02    b         4         4
2015-01-02    i         5         7
2015-01-02    <null>    5         <null>
2015-01-02    i         2         7

However, with the following query:

SELECT t1.*, t2.sum_Col3
From Table1 as t1
left outer Join Table2 as t2
ON t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2;

I'm getting the table:

Table 1:
Col1:         Col2:     Col3:     sum_Col3:   col4, col5, col6...
2015-01-01    b         3         <null>
2015-01-01    <null>    5         <null>
2015-01-01    b         5         <null> 
2015-01-01    i         6         <null>
2015-01-02    b         4         <null>
2015-01-02    i         5         <null>
2015-01-02    <null>    5         <null>
2015-01-02    i         2         <null>

Any insight anyone?

Thank you in advance!

CodePudding user response：

You should at least change the join expression because it contains an obvious mistake:

from

ON t1.Col1 = t1.Col1 AND t1.Col2 = t2.Col2;

ON t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2;

CodePudding user response：

I'm not sure why I got null values but, as user @NishantGupta stated, I was making a mistake writing "count(Col3)" instead "sum(Col3)" on the query.

Fixing this, solved the problem.