I need to count the number of cities occurrences on 2 different tables. One Table is the Supplier table which has supplier_id, City and State. The second table is consumer with consumer id , City and State. My query needs to return the State, City number of supplier cities and number of consumer cities where the names match.
I have tried a couple different things like intersect and union all but cant get it My latest is below but it is Not sure what I am doing wrong
SELECT S.State ,C.City ,count(S.City) as Number_Of_Suppliers ,count(C.City) as Number_Of_Consumers from dbo.Tb_Supplier S left outer Join dbo.Tb_Consumer C On S.STATE = C.STATE AND S.City = C.City group by S.state ,C.City
Advertisement
Answer
Use union all and group by:
select state, city, sum(supplier)
sum(supplier) as Number_Of_Suppliers,
sum(consumer) as Number_Of_Consumers
from ((select state, city, 1 as supplier, 0 as consumer
from dbo.Tb_Supplier s
) union all
(select state, city, 0 as supplier, 1 as consumer
from dbo.Tb_Consumer c
)
) sc
group by state, city