CODEWITHSUNDEEP
sql-servercountcasedifference
M.R
M.R

Reputation: 59

how to count quantity of different between 2 tables?

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

for example table 1:

enter image description here

table 2:

enter image description here

and i want to get this table:

enter image description here

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!

Upvotes: 0

Views: 28

Answers (1)

Wouter
Wouter

Reputation: 2976

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

Upvotes: 2

Related Questions