HiveQL query to check that rows A + B = row C

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

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!

Re: HiveQL query to check that rows A + B = row C

Pzahid — Fri, 06 Sep 2024 17:21:12 GMT

@Supernova I have tried the same query in my cluster and I got the correct result.

+-----------+--------------+
| currency | total_check |
+-----------+--------------+
| CAD | 0.0 |
| CNH | 0.0 |
| AED | 0.0 |
| AUD | 0.0 |
| CHF | 0.0 |
+-----------+--------------+

Can you share the complete table definition please.

question HiveQL query to check that rows A + B = row C in Support Questions

HiveQL query to check that rows A + B = row C

Re: HiveQL query to check that rows A + B = row C