SQL, combine results and return with 2 decimal places-CodePudding

I have two tables, one with employee information including sales persons and the other with transactions. I need to query both and return a calculated commission of 2.5%, first and last names. What I have returns this but to three decimal places instead of two and is it possible to combine the results to return one for each person?

SELECT fName, lName, Amount_Due * 0.025 AS Commission
FROM EMPLOYEE, "TRANSACTION"
WHERE  EMPLOYEE.Eid = "TRANSACTION".eID

Result

fNAME	lNAME	Commission
Jane	Doe	20.00
Jane	Doe	12.00
Jack	Jones	10.000
Jack	Jones	15.000
John	Doe	12.000
John	Doe	14.00

Thanks for any help

CodePudding user response：

use these sql math functions round(), sum() and aggregation GROUP BY to achieve your requirements.

SELECT e.fName, e.lName, round(sum(t.Amount_Due * 0.025), 3) AS Commission
FROM EMPLOYEE e
INNER JOIN "TRANSACTION" t on e.Eid = t.eID
GROUP BY e.lName, e.fName