how to count quantity of different between 2 tables?

Question

i need to count different between 2 tables (same table from 2 different days) to see what have changed.

for example table 1:

table 2:

and i want to get this table:

i try this code:

select a.of_key , case when a.color != b.color then count (a.color) ELSE 0 END AS color,  
        case when a.side != b.side then count (a.side) else 0 end as side   
from 130720 A right JOIN 100720 B
ON a.of_key = b.of_key and a.num = b.bum
group by a.of_key

it is not working

please help

thanks!

Answer 1

Put your aggregate outside the case statement:

select a.of_key , SUM(case when a.color != b.color then 1 ELSE 0 END) AS color,  
        SUM(case when a.side != b.side then 1 else 0 end) as side   
from 130720 A right JOIN 100720 B
ON a.of_key = b.of_key and a.num = b.bum
group by a.of_key