0

I am trying to calculate a proportion of a portion to a total. My table looks like this:

State City Money_Awarded
NY NYC 5
NY QBC 7
NJ NJC 8
NJ ABC 6

I have written a query that gives me totals per state (using SUM and GROUP BY state), but I am having trouble returning another column that would calculate the proportion of EACH state to the total. I could write a sum query for the Money_Awarded column and then use the result, but I'd like to do this programmatically. How can I do this? I am using MySQL

Willy_Golden
  • 51
  • 1
  • 7

1 Answers1

0

You can use window functions:

select state, sum(money_awarded),
       sum(money_awarded) * 1.0 / sum(sum(money_awarded)) over () as ratio
from t
group by state;

EDIT:

Older versions of MySQL do not support window functions, so you can instead do:

select t.state, sum(t.money_awarded),
       sum(t.money_awarded) * 1.0 / tt.total_money_awarded as ratio
from t cross join
     (select sum(money_awarded) as total_money_awarded
      from t
     ) tt
group by t.state, tt.total_money_awarded
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I just tried this and it did not work. I got an error. Are there any other techniques? It told me that "Queries can not contain both windowed and non-windowed aggregation" – Willy_Golden Dec 16 '20 at 20:09