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