question Hive query to check sum of 2 rows against a 3rd row in Support Questions

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

Supernova — Tue, 21 Apr 2026 06:27:40 GMT

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,

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

ChethanYM — Wed, 07 Aug 2024 05:00:23 GMT

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

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

Supernova — Wed, 07 Aug 2024 14:39:16 GMT

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).