CODEWITHSUNDEEP
mysql
Faisal Arif
Faisal Arif

Reputation: 55

how to get count of values from multiple columns

i have users_details table with the following columns

martial_status | religion | education

i want to get count of values from this table as below. 

married (100)
single (200)
Muslim(50)
Jews (50)
Christian(50)
Graduates(100) 
etc, etc.

I do not know if it is possible to get all of the counts using single SELECT , please help.

Upvotes: 1

Views: 167

Answers (4)

juergen d
juergen d

Reputation: 204766

You can do something like this:

select sum(case when religion = 'Muslim'
           then 1
           else 0 
           end) as muslims,
       sum(case when martial_status = 'married'
           then 1
           else 0 
           end) as married
       ...
from your_table

A shorter version of that would be:

select sum(religion = 'Muslim') as muslims,
       sum(martial_status = 'married') as married
       ...
from your_table

Upvotes: 0

MvG
MvG

Reputation: 60868

Using a single select with no subqueries or similar is tricky, but I believe that a single query should work for you just as well. You can have a single select to obtain all the counts for a given column, and then use union all to merge the results so that a single query will get you all the counts of all the columns.

select martial_status, count(*) from user_details group by martial_status
union all
select religion, count(*) from user_details group by religion
union all
select education, count(*) from user_details group by education

In case you have NULL values in your table, you might want to omit those from the results.

If it really is a single select which you need, then you would have to alter the layout of your table, but I cannot imagine a valid reason for such a requirement.

Upvotes: 0

Sirko
Sirko

Reputation: 74046

It's not exactly a single SELECT-statement, but you could retrieve it with one query as follows (assuming the columns are compatible):

( SELECT `martial_status`, COUNT(*) as `cnt` FROM user_details GROUP BY `martial_status` )
UNION
( SELECT `religion`, COUNT(*) as `cnt` FROM user_details GROUP BY `religion` )
UNION
( SELECT `education `, COUNT(*) as `cnt` FROM user_details GROUP BY `education` )

Upvotes: 1

Kshitij
Kshitij

Reputation: 8614

You need to deal with each column seperately and then union it- 

  SELECT martial_status, count(*)
  FROM USER_DETAILS
  GROUP BY martial_status
UNION ALL
  SELECT religion, count(*)
  FROM USER_DETAILS
  GROUP BY religion
UNION ALL
  SELECT education, count(*)
  FROM USER_DETAILS
  GROUP BY education

Upvotes: 1

Related Questions