I need help to create new columns to calculate total of each employee salary and bonus
id Name PaymentType Payment
---- ----- ------ -------
1 John Salary 100
2 Peter Salary 100
3 John Bonus 20
4 Russel Salary 100
5 Bill Salary 100
6 Bill Bonus 40
7 John Salary 100
How can I make something like below
Name Salary Bonus
John 200 20
Peter 100 0
Russel 100 0
Bill 100 40
CodePudding user response:
We can use conditional aggregation here:
SELECT
Name,
SUM(CASE WHEN PaymentType = 'Salary' THEN Payment ELSE 0 END) AS Salary,
SUM(CASE WHEN PaymentType = 'Bonus' THEN Payment ELSE 0 END) AS Bonus
FROM yourTable
GROUP BY Name;