Need a SQL query to get data based on condition based on count

Question

I have tables called areadetail and state.

areadetail has these columns:

State Id
Area Id-rural(1) or urban(2)
Population

Sample data:

Stateid                AreaID                    Population
1                        1                           10
1                        2                           20
1                        2                           20
2                        1                           10
2                        2                           20
3                        1                           10 

State table has these columns:

State name and state id

Sample data:

State Id                    StateName
1                             Delhi
2                             Mumbai
3                             Jaipur

Now I need a query to display records like

State name      Rural               Urban
------------------------------------------
Delhi            3                   2
Mumbai           1                   1
Jaipur           1                   0

Answer 1

I guess AreaID=1 means RURAL and 2 is URBAN then try this query:

SELECT 
  MAX(s.StateName),
  SUM(CASE WHEN ad.AreaID=1 THEN ad.Population ELSE 0 END) as Rural ,
  SUM(CASE WHEN ad.AreaID=2 THEN ad.Population ELSE 0 END) as Urban


FROM State as s
LEFT JOIN areadetail as ad on s.Stateid=ad.Stateid
GROUP BY s.Stateid

SQLFiddle demo