@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. ... View more

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

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 ... View more

Thanks! because i have null values in my data set as well, i used colaesce, and it worked! Your query was the basis though, so thanks again! Query by @nramanaiah that worked for me, as I have null records in the dataset: select Currency, (coalesce(spend_a,0)) + (colaesce(spend_b,0)) + coalesce(spend_c,0)) + coalesce(spend_d,0)) as total_spend from test   ... View more

I assume you meant NVL function returns 0 or 1 if a null is found, or the specified expression in the argument - when using Hive. With Impala and NVL2 you would still need to have the outer query to "sum" up all the 1 values what we have mapped from the column value (from their real value to 0 or to 1). It would just be a bit nicer, but no real change. ... View more