Incorrect math being returned on rows when aggregating values together

[#1]

So I have 2 values from different tables that i'm trying to join and aggregate. The problem is that i get results where the math doesn't add up at times- and have tried changing the join\inner join, group by, select distinct, cast- it doesn't seem to work. I need coalesce, as there are null values.

Dataset:

Table Sales:

Currency	ColA	ColB	ColC	DateField
AUD	208,883	{null}	{null}	'2024-06-30'
CAD	215,913	{null}	{null}	'2024-06-30'
MZN	0	0	{null}	'2024-06-30'
YER	0	{null}	10	'2024-06-30'

Table OTHER_Sales:

Currency	Col1	Col2	Col3	DateField
AUD	{null}	208,883	20	'2024-06-30'
CAD	{null}	215,913	0	'2024-06-30'
MZN	{null}	0	13	'2024-06-30'
YER	{null}	0	0	'2024-06-30'

Query:

SELECT a.currency as currency, SUM((coalesce(a.ColA,0)) + (coalesce(b.Col2))) as sales_check

FROM

db.sales a inner join db.OTHER_sales b

ON a.currency = b.currency

WHERE a.DateField = '2024-06-30'

GROUP BY a.currency

The incorrect results that I currently get:

currency	sales_check
AUD	-1645761638.790000
CAD	5855379123.770950
MZN	0
YER	0

The correct results that I need to get:

currency	sales_check
AUD	0
CAD	0
MZN	0
YER	0

Thanks!

[#2]

Hi @Supernova 

Can you try this?

SELECT
a.currency as currency,
SUM(coalesce(a.ColA, 0) + coalesce(a.ColB, 0) + coalesce(a.ColC, 0) + coalesce(b.Col1, 0) + coalesce(b.Col2, 0) + coalesce(b.Col3, 0)) as sales_check
FROM
db.sales a
INNER JOIN
db.OTHER_sales b
ON
a.currency = b.currency
WHERE
a.DateField = '2024-06-30'
AND b.DateField = '2024-06-30'
GROUP BY
a.currency;

Regards,

Chethan YM