CODEWITHSUNDEEP
mysqlsql
Abhijit Shelar
Abhijit Shelar

Reputation: 1065

How to write single query for column with different type

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

Answers (1)

John Woo
John Woo

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

Related Questions