How to use sum in MYSQL

Question

This my code and I want a output like in the picture:

SELECT `test`.custinfo.CustID, OtherDeductionName AS DeductionName, sum(OtherDeductionAmount) as DeductionAmount FROM `db_payroll`.`tbl_otherdeductions`
LEFT JOIN `db_payroll`.tbl_payroll
ON `db_payroll`.tbl_payroll.OtherDeductionsID = `db_payroll`.tbl_otherdeductions.OtherDeductionsID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28' 
AND `db_payroll`.`tbl_payroll`.OtherDeductionsID <> ''
GROUP BY DeductionName
UNION
SELECT `test`.custinfo.CustID, StatutoryDeductionsName AS DeductionName, sum(StatutoryEE) as DeductionAmount FROM `test`.`tbl_statutorydeductions`
LEFT JOIN `test`.tbl_statutorydeductionstype
ON `test`.tbl_statutorydeductions.StatutoryDeductionsTypeID = `test`.tbl_statutorydeductionstype.StatutoryDeductionsTypeID
LEFT JOIN `db_payroll`.tbl_payroll
ON `db_payroll`.tbl_payroll.StatutoryDeductionsID = `test`.tbl_statutorydeductions.StatutoryDeductionsID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28'
AND `db_payroll`.`tbl_payroll`.StatutoryDeductionsID <> ''
GROUP BY DeductionName
UNION
SELECT `test`.custinfo.CustID, TypeOfLoan AS DeductionName, sum(AmortAmount) as DeductionAmount FROM `test`.`tbl_benamortloan`
LEFT JOIN `db_payroll`.tbl_payroll
ON `test`.tbl_benamortloan.IDno = `db_payroll`.tbl_payroll.EmpID AND `test`.tbl_benamortloan.CutOffID = `db_payroll`.tbl_payroll.CutOffID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE  `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28'
AND `db_payroll`.`tbl_payroll`.LoanPaymentsID <> ''
GROUP BY DeductionName
ORDER BY DeductionName

This is the output I want

Can you help me with this

First, what does this have to do with c#? Second, questions like this are difficult at best because we don't have your database and data. So I will also ask what results are you experiencing? — Crowcoder, Dec 06 '22 at 01:25
This is the result: 00000008 Car Loan Deduction 7500 00000008 INTERNET LOAD ALLOWANCE -3500 00000008 LOAD ALLOWANCE -12400 00000008 Mandatory Provident Fund 3645 00000008 PAG-IBIG - Salary Loan 3257.54 00000008 PAGIBIG 5800 00000008 PhilHealth 24226.370000000017 00000008 SSS 44505 00000008 SSS - Salary Loan 2500 00000008 TAX 30456.479999999996 i want to sum all the amount — allan, Dec 06 '22 at 01:47

Mike Robinson · Answer 1 · 2022-12-06T03:49:44.627

Well, what I see so far is a great big UNION query which will produce three concatenated groups of data, each one containing sums. So maybe now all you need to do is something like:

SELECT SUM(DeductionAmount) FROM
(
  ..insert the text of your present query here..
)

Of course, this is the simplest example.

The key idea is this: "your present query" – textually included, within parentheses – is used as a subquery of a primary query that calculates the sum of all of the DeductionAmount columns now being provided by "your present query." "Your present query" is the source of the rows seen by the outer-level one, and what you finally get is "the result of the outer-level one."

(Always separately review the result of your intended subquery, to be sure that it is consistent and correct, before wrapping it into an outer-level query. "Query bugs" can be hard to diagnose otherwise.)

How to use sum in MYSQL

1 Answers1