Get a percentage in a pivot mysql query

Question

My data looks like

 EmployeeId      paycategory            value

      1             Contribution         200
      1              Salary              18000

I use the following query,

select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid

The above shows the results as,

EmployeeId        Salary      Contribution
      1             18000           200

which is great. Now I want to show the percentage of contribution to salary

So I expect something like,

EmployeeId        Salary      Contribution      Contribution Percentage
      1             18000           200            1.11 -- (200/18000)*100

How do I go about it using a pivot query if possible ?

Answer 1

You can use subquery for it like below:

select 
employeeid, 
salary, 
contribution, 
(contribution/salary)*100 Contribution_Percentage 
from (
select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid 
) t

DEMO on Fiddle

For MySql 8.0 you can use Common Table Expression like below:

with cte as (
select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid 
)
select 
employeeid, 
salary, 
contribution, (contribution/salary)*100 Contribution_Percentage
from cte

DEMO