I have two tables and when I join and do group by, I am able to sum the Hours column but the Unpaid_Balance column gets aggregated multiple times. For example, Smith worked a total of 21 hours and his hourly rate is $30 so that comes out to $630. After calculating $630 I would like to add his Unpaid_Balance which is in dollars of $400 for a total $1,030 but instead, what I get is $630 + $400 + $400 + $400 or $1,830.
How is it possible to groupby and aggregate a column, in this case Hours, multiplied by $30, and then add the Unpaid_Balance after doing the groupby? Below is my code and I also tried SUM((Hours*30)) + Unpaid_balance as amount_due but that didn't work.
select
workers.name,
SUM((Hours*30) + Unpaid_Balance) as amount_due
FROM Workers
LEFT JOIN hours
ON workers.id = hours.id
GROUP BY workers.name
ORDER BY workers.name;
Table 1
ID Hours
1 5
1 5
1 8
2 10
Table 2
ID Name Unpaid_Balance
1 Smith 400
2 Ryan 10
If Unpaid_Balance is fixed for any hour per each individual, then just add another aggregation for this column such as MAX() or MIN()
SELECT w.name, SUM(Hours * 30) + MAX(Unpaid_Balance) AS amount_due
FROM Workers w
LEFT JOIN hours h
ON w.id = h.id
GROUP BY w.name
ORDER BY w.name;