CODEWITHSUNDEEP
mysqlsql
vina
vina

Reputation: 23

MySQL: Get total hours, then multiply with rate, then rounding in one SELECT statement

My data looks like this:

UserID  Hours   BillRate
1   1.50    2.25
1   2.50    3.25
1   3.50    3.25
2   5.50    4.25
2   6.50    5.25
2   7.50    5.25

Is there anyway that I can get total spend for each person in one SELECT statement? This is what I have. It returns what I want, but I have to do 2 SELECT statements.

SELECT UserID, SUM(OneSpend) AS TotalSpend
FROM (
    SELECT UserID, ROUND(SUM(Hours)*BillRate,2) AS OneSpend
    FROM mytable
    GROUP BY UserID, BillRate
) a
GROUP BY UserID

Upvotes: 1

Views: 162

Answers (1)

Barmar
Barmar

Reputation: 781833

SELECT UserID, ROUND(SUM(Hours*BillRate), 2) AS TotalSpend
FROM mytable
GROUP BY UserID

According to the distributive property of multiplication over addition, SUM(Hours)*BillRate is the same as SUM(Hours * BillRate) when all the BillRate values are the same, as they are when you group by BillRate.

Upvotes: 2

Related Questions