How to calculate % of total in MySQL

Question

I have the following qry:

select count(*) as headct
, case
when year(DOB) <= 1945 then 'Traditionalist'
when year(DOB) >= 1946 and year(DOB) < 1965 then 'Baby Boomer'
when year(DOB) >= 1965 and year(DOB) < 1981 then 'Gen X'
when year(DOB) >= 1981 then 'Gen Y'
end as generation
from ret_active
group by Generation with rollup;

It returns this table:

headct    generation
--------------------------
1820      Baby Boomer
2208      Gen X
883       Gen Y
17        Traditionalist
4928      null

How can I add another column where I can show the % of total for each category? In other words, how can I get the following results:

headct    generation      % of Total
-------------------------------------
1820      Baby Boomer     37%
2208      Gen Xm          45%
883       Gen Y           18%
17        Traditionalist  0%
4928      null            100%

Thanks!

score 0 · Accepted Answer · answered Mar 28 '14 at 17:08

select count(*) as headct
, case
    when year(DOB) <= 1945 then 'Traditionalist'
    when year(DOB) >= 1946 and year(DOB) < 1965 then 'Baby Boomer'
    when year(DOB) >= 1965 and year(DOB) < 1981 then 'Gen X'
    when year(DOB) >= 1981 then 'Gen Y'
  end as generation
, concat(round(count(*)/
    (select count(*) from ret_active)*100,0),'%') as '% of Total'
from ret_active
group by Generation with rollup

How to calculate % of total in MySQL

1 Answers1