0

I have this query to get the percentages of different values in a specific column:

select dma, count(*) / const.cnt *100 as my_percent 
from full_db3 cross join
 (select count(*) as cnt from full_db3) const
group by dma
order by my_percent desc limit 10;

Step one: I need to amend this so it gives me the top 10 values in the column dma based on the value of another column. For example, if the value in column gender is "f", what are the top 10 values in column dma. Something like this (which doesn't work, of course).

select dma, count(*) where gender = 'female' / const.cnt *100 as my_percent 
from full_db3 cross join
 (select count(*) as cnt from full_db3) const
group by dma
order by my_percent desc limit 10;

The above throws an error, but should get the point across.

Step 2 would be looping through all my columns and returning the top 10 values in every column (not just dma) give the value of a specific column (e.g., gender= 'female'). What's the best way to do this?

jonmrich
  • 4,233
  • 5
  • 42
  • 94
  • 1
    Step 1: Do you want to get the percentage of females by dma compared to the whole table? You could write `SUM(IF(gender = 'female',1,0)) / const.cnt *100` or just add add normal `WHERE gender = 'female'` clause at the end (returns same results). Step 2: you will have to explicitly write `IF` clauses for every column. – Simo Kivistö Apr 01 '15 at 19:59
  • @SimoKivistö I figured out where to put the `WHERE` clause. That was a big help. For step 2, I have 400+ columns, is there any shortcut you can think of? – jonmrich Apr 01 '15 at 23:46
  • Not really. Take a look at http://stackoverflow.com/q/22369336/1288184 and http://stackoverflow.com/q/2179207/1288184 as the problem is somewhat similar. You could use procedures or scripting for example... – Simo Kivistö Apr 02 '15 at 08:26

0 Answers0