My query almost outputs the required data, but not in the format I'm after. It is producing a line item per trainer as the wage_type in not unique (can be 3 types), and the amounts can be anything.
SELECT
trainerid, name,
wage_period, wage_type,
amount,
SUM(amount)
FROM
course_local_staff_wages
INNER JOIN
course_local_trainers ON course_local_staff_wages.trainerid = course_local_trainers.userid
WHERE
wage_period = '11-2021'
GROUP BY
Trainerid, Name, Wage_Period, wage_type, amount
I am getting the individual lines per trainerid, but would like the output to be a single line per trainer with the output as follows:
Trainerid, Name, Wage_Period, Invoice Amt, Trainer Fees, Other, Total (of the 3 columns Trainer Fees, Other)
Something like this:
Trainer Id Name Wage_Period InvoiceAmt TrainerFees Other Total
----------------------------------------------------------------------------
12345 Jack Sparrow 11-2021 21.10 1000 100 1121.10
45632 Tony White 11-2021 10 900 50 960
Grand Total 2081.10
The Grand Total is a nice to have.
I'm assuming a subquery to do it based on the initial results, but I don't have a clue how to achieve this.
Appreciate any help.
