Calculate Distinct count when joining two tables

Question

id1     id2    year State Gender    
====  ====== ====== ===== =======    
1       A      2008    ca      M    
1       B      2008    ca      M    
3       A      2009    ny      F   
3       A      2008    ny      F     
4       A      2009    tx      F

select
    state, gender, [year],
    count (distinct(cast(id1 as varchar(10)) + id2))
from
    tabl1
group by state, gender, [year]

i could find the distinct count through statewise. now i need to find distinct count through city wise. like in CA - 3 cities.. sfo,la,sanjose. i have a look up table that i could find the state and the city.

    table2 -  city     
    ====           
   cityid  name
     ==== ====
     1   sfo                
     2   la                    
     3   sanjose 

    table 3 - state
     ====   
stateid   name
   ==== ====
    1    CA
    2    Az

table 4 lookup state city
 ====   
pk_cityId pk_state_id
1             1
2             1

select state,city,gender, [year],
        count (distinct(cast(id1 as varchar(10)) + id2))
    from
        tabl1 p
    group by state, gender, [year],city

this query to find city and state name.

select c.city,s.state from city_state sc
inner join (select * from state)s on sc.state_id = s.state_id
inner join (select * from city)c on sc.city_id = c.city_id

i did similar to this query using the look up table but the problem is that i get the distinct count throughout the states and the same no of count is repeating for each city in the state.

ex: for count for ca : 10 then the count for cities should be like La - 5, sanjose - 4, sfo-1.

but with my query i get as sfo - 10,la-10, sanjose-10.. i couldnt find the count for the lower level. any help would be appreciated.

UPDATE: i have updated the query and the lookup tables.

Answer 1

Your implied schema seems to have a flaw:

You're trying to get city level aggregates but you are joining your data table (table1) to your city table (table2) based on the state. This will cause EVERY city in the same state to have the same aggregate values; in your case: all California states having count of 10.

Can you provide actual DDL statements for your two tables? Perhaps you have other columns there (city_id?) that might provide the necessary data for you to correct your query.