how to compute percentage in mysql/sql when 2 group by conditions are present

id title   count organizer
1  music   4     2
2  sports  6     2
3  music   2     3

I have a derived table with the above structure. I need to compute the percentage of the number of events of each organizer for each title. i.e. I’m expecting a result like:

organizer  title   percentage
2          music   40%
2          sports  60%
3          music   100%

The way I’m doing it without organizer in consideration produces the percentage aggregating all the values easily but introducing organizer messes it all up. Can anyone help me here??

Answer

Taking your derived table as the actual data (may not be optimal) you could:

select to.organizer, to.title, 100.0*to.count/o.count as percentage
from
  (select organizer, sum(count) as count from derivedtable group by organizer) o
  inner join
  derivedtable to 
  on
    to.organizer = o.organizer

It works by summing the data per organizer to get a total, and joining this back to the data you have so you can do the particular event-organizer count divided by the total count for that organiser

There might have been a simpler way to do it with your source data, as is you’ll have to plug your query in that creates your derived table, possibly making it messy. Probably simplest to do as a CTE, but do try to include original source data and “query so far” next time you ask a question, otherwise we have to build a solution on top of some solution we know nothing about and the result might not be optimal