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!
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments