So I can't seem to sum up correctly to show that there is no difference, even though I've verified in Excel that there is no difference- and that Column A + Column B = Column C in all cases. It appears to be adding or subtracting incorrectly at times- no matter if I use minus or add(even tried switching them around), or using ABS(absolute).
Data from table Sales:
| Currency | ColA | ColB | Total_ColC |
| AED | 2,600,0000 | {null} | 2,600,000 |
| AUD | 465,000 | (406,000) | 59,000 |
| CAD | (1,450,000,000) | 35,000,000 | (1,415,000,000) |
| CHF | 490,000 | (114,000) | (376,000) |
| CNH | (216,000) | 2,200,000 | 1,984,000 |
This is the query that I'm trying to double-check that the sum of column ColA + ColB = column Total_ColC:
SELECT currency,
CASE
WHEN SUM(COALESCE(ColA,0)) + SUM(COALESCE(ColB,0)) = (SUM(COALESCE(Total_ColC,0)))
THEN 0
ELSE SUM(COALESCE(ColA,0)) + SUM(COALESCE(ColB,0)) - (SUM(COALESCE(Total_ColC,0)))
END AS Total_Check
FROM `reports`.sales
WHERE DateOfBusiness = '2024-08-10'
GROUP BY Currency
Here are the incorrect results that I get for some rows- notably, anything that has a negative value.
| Currency | Total_Check |
| AED | 0 |
| AUD | (871,000) |
| CAD | (2,900,000,000) |
| CHF | (228,000) |
| CNH | (4,400,000) |
I expect for all rows in Total_Check to be 0...
Thanks!
