Hive query to check sum of 2 rows against a 3rd row

[#1]

Hey all,

So I'm trying to check some columns together to ensure that the sums of A & B equal to row C, for each given currency.

Table Sales;

Currency	ColA	ColB	ColC
AUD	50,000	50,000	100,000
CAD	{null}	20,000	20,000
MZN	30,000	{null}	30,000
YER	10,000	10,000	20,000

Query that I can't seem to get working properly:

SELECT Currency,  CAST((SUM)CASE WHEN((coalesce(ColA,0)) + (coalesce(ColB,0))) = (coalesce(ColC,0)) THEN 0 ELSE SUM((coalesce(ColA,0)) + (coalesce(ColB,0))) - (coalesce(ColC,0) END AS sales_check)

The output that I'd like to get:

Currency	sales_check
AUD	0
CAD	0
MZN	0
YER	0

Thanks,

[#2]

Hi @Supernova ,

Can you try this below?

SELECT
Currency,
CASE
WHEN COALESCE(SUM(ColA), 0) + COALESCE(SUM(ColB), 0) = COALESCE(SUM(ColC), 0)
THEN 0
ELSE COALESCE(SUM(ColA), 0) + COALESCE(SUM(ColB), 0) - COALESCE(SUM(ColC), 0)
END AS sales_check
FROM Sales
GROUP BY Currency;

Regards,

Chethan YM

[#3]

After adding a ) to both lines(which Hive didn't like not having), it works for some of the currencies- but others are not adding up properly- ie, they'll double the negative values(when there).