CODEWITHSUNDEEP
mysqlsqldatabasecountdatacolumn
Mufaddal
Mufaddal

Reputation: 566

Fetch Column Names of a Table for Columns with Top 3 count

I need the the Column Names in a table which are the Top Three Columns with the highest Count.

I am using mySQL:

I was able to get the count of the required columns using this query:

SELECT 
COUNT(unsafe_spaces_home) AS AH ,
COUNT(unsafe_spaces_school) AS SCH ,
COUNT(unsafe_spaces_market_place) AS MP ,
COUNT(unsafe_spaces_field_or_playground) AS PG ,
COUNT(unsafe_spaces_dumping_ground) AS DG ,
COUNT(unsafe_spaces_railway_station) AS RS ,
COUNT(unsafe_spaces_route_to_toilet) AS RT ,
COUNT(unsafe_spaces_toilet) AS ST ,
COUNT(unsafe_spaces_well_or_water_pump) AS WT ,
COUNT(unsafe_spaces_river_pond) AS RP ,
COUNT(unsafe_spaces_sewer) AS SS 
FROM formdata

I get the result as Follows:

AH  SCH MP  PG  DG  RS  RT  ST  WT  RP  SS
===========================================
 0  0   12  1   7   16  2   9   0   9   1

However I need the required result something like this:

  top_3_columns
 ================
   RS,MP,ST

How can I achieve this?

Upvotes: 3

Views: 82

Answers (1)

aru007
aru007

Reputation: 380

Consider column names as a,b,c,d,and e Then you can use following query

select  tagName, value from 
(select  'a'  tagName, count(a)  value from formdata  UNION
select  'b'  tagName, count(b)  value from formdata  UNION
select   'c'  tagName, count(c)  value from formdata  UNION
select  'd'  tagName, count(d)  value from formdata  UNION
select   'e'  tagName, count(e)  value from formdata  
) 
as results
order by value desc 
limit 3;

Upvotes: 1

Related Questions