How to groupby and then add a value-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

You should be calculating total work hours from the hours table first then join the hours table's aggregate result with the workers table, that should work

select workers.name,(total_hours.hours * 30   Unpaid_Balance) from workers left join 
 (select sum(hours) hours,id from hours group by id) total_hours on total_hours.id = workers.id

Running example in Postgres is here