sql - MySQL calculate percentage of two other calculated sums including a group by month

Question

Say i have 3 tables with a model like this

The result i want to have now looks like this

I want to calculate turnovers and profits made by all employees per month and compare it to the last years SAME month and calculate the difference in percentage of the profits. It should include the last 12 months with the INTERVAL function.

select
  bookings.b_emp_id as "Employee",
  MONTH(bookings.b_date) as Month,
  @turnover1 := sum(bookings.b_turnover) as '2017-turnover',
  @turnover2 := (select sum(lx.b_turnover) 
                 from bookings as lx 
                 where lx.b_date = date_add(bookings.b_date, INTERVAL -1 YEAR)
                 GROUP BY 
                   MONTH(bookings.b_date),
                   YEAR(bookings.b_date), 
                   bookings.b_emp_id
                ) as '2016-turnover',
  sum(b_profit) as '2017-profit',
  @profit1 := (select sum(lx.umsatz_fees) 
               from bookings as lx 
               where lx.b_date = date_add(bookings.b_date,INTERVAL -1 YEAR)
               GROUP BY 
                 MONTH(bookings.b_date),
                 YEAR(bookings.b_date),
                 bookings.b_emp_id
              ) as '2016-profit'
from bookings 
where bookings.b_date > '2017-01-01'
  and bookings.b_emp_id = ′SA′ 
GROUP BY MONTH(bookings.b_date)
order by bookings.b_date desc

Answer 1

Use conditional aggregation. It is not clear if you want to look at the last 12 / 24 months, or at the months of 2017 and the same months in 2016. Neither do I understand how you want to calculate a percentage. I divide this year's profits by last year's in below query. Adjust this so it meets your needs.

select
  b_emp_id,
  month,
  turnover_this_year,
  profit_this_year,
  turnover_last_year,
  profit_last_year,
  profit_this_year / profit_last_year * 100 as diff
from
(
  select
    b_emp_id,
    month(b_date) as month,
    sum(case when year(b_date) = year(curdate()) then b_turnover end) as turnover_this_year,
    sum(case when year(b_date) = year(curdate()) then b_profit end) as profit_this_year,
    sum(case when year(b_date) < year(curdate()) then b_turnover end) as turnover_last_year,
    sum(case when year(b_date) < year(curdate()) then b_profit end) as profit_last_year
  from bookings
  where year(b_date) in (year(curdate()), year(curdate()) - 1)
    and month(b_date) <= month(curdate())
  group by b_emp_id, month(b_date)
) figures
order by b_emp_id, month;