CODEWITHSUNDEEP
phpmysqlsqlsum
Satyam
Satyam

Reputation: 13

SUM of column in PHP gives wrong output

I am facing trouble while executing and doing the SUM of the column but the code is showing the wrong output. I tried to fix this through GROUP BY also but the same wrong output result. I believe this is executing times of common row existing in DB.

I truly never facing this issue before. Please help me , How to I fix this issue and what should be the correct SQL in PHP

*Table CR_table*

ID | credit | components_key
----------------------------
1  | 400    | 202012162458
----------------------------
2  | 300    | 202012162458


*Table DR_table*

ID | req_debit  | components_key
----------------------------
1  | 100    | 202012162458
----------------------------


SELECT DISTINCT SUM(credit)-SUM(req_debit) FROM CR_table LEFT JOIN DR_table ON CR_table.components_key = DR_table.components_key WHERE DR_table.transaction_type = 'OA' AND CR_table.components_key = '202012162458' GROUP BY CR_table.components_key

OUTPUT = 500 (BUT the Output Should be 600)

OPENING BALANCE

SELECT SUM(AMT) as OpeningBalance (SUM(CASE WHEN DR.insert_date < '2020-12-29' THEN CR.credit - DR.debit ELSE 0 END) FROM CR_table CR WHERE CR.components_key = '202012162458'
UNION ALL SELECT -req_debit AMT FROM DR_table DR WHERE DR.transaction_type = 'OA' AND DR.components_key = '202012162458') t  `but Opeing is also not working

`

Upvotes: 0

Views: 66

Answers (1)

Popeye
Popeye

Reputation: 35930

When you are joining two tables, it is producing 2(credit)*1(debit) = 2 records in which you have 2 different credits and one single debit repeated in both rows so you are getting 500 (400 + 300 - 100 - 100) output.

You can also use UNION ALL as follows:

SELECT SUM(AMT) FROM
(SELECT credit AMT FROM CR_table CR  
 WHERE CR.components_key = '202012162458'
UNION ALL
SELECT -req_debit AMT FROM DR_table DR 
 WHERE DR.transaction_type = 'OA' 
   AND DR.components_key = '202012162458') t

Upvotes: 1

Related Questions