Reputation: 1065
I have database of state in which I have data of people who are physically challenged? State in turn has many cities
There are types of people base on leg problem, hand problem, blind, etc.
I have only one state
people_info (cityname, type)
I need to show state data as
State name |total people count | leg problem count| hand problem count|blind count
Same way
city A |total people count | leg problem count| hand problem count|blind count
city B |total people count | leg problem count| hand problem count|blind count
How to write single query for state and city in order to print data
Upvotes: 1
Views: 91
Reputation: 263943
Since you haven't mentioned the RDBMS you are using, this will somewhat work on many (but not all) database server.
SELECT cityName,
COUNT(*) totalPeople,
SUM(CASE WHEN type = 'legProb' THEN 1 ELSE 0 END) AS LegProbCount,
SUM(CASE WHEN type = 'handProb' THEN 1 ELSE 0 END) AS HandProbCount,
SUM(CASE WHEN type = 'blindProb' THEN 1 ELSE 0 END) AS blindCount
FROM people_info
GROUP BY cityName
In MySQL
, you can use this directly,
SELECT cityName,
COUNT(*) totalPeople,
SUM(type = 'legProb') AS LegProbCount,
SUM(type = 'handProb') AS HandProbCount,
SUM(type = 'blindProb') AS blindCount
FROM people_info
GROUP BY cityName
Upvotes: 1