Skip to content
Advertisement

Count city and State from 2 different tables

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement