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

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

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

Hi all, I have a query that I've found here thanks to @mszurap which I've tweaked and am trying to get working. I have a field called "RunNumber" that identifies if\when data is appended- it can have a value of 1 or 2 usually, but is not limited to 2. I can't use Group By, as you'll see in my query- there are hundreds and thousands of CASE statements. I've tried and cannot seem to get it working in Hive. I get a "..Expression not in GROUP BY key '0'..."   Query:   SELECT MaxRun = MAX(RunNumber)   ,FirstName = SUM(CASE WHEN FirstName IS NULL OR FirstName = 'NULL' OR FirstName = ' ' THEN 1 ELSE 0 END) AS FirstName ,LastName = SUM(CASE WHEN LastName IS NULL OR LastName = 'NULL' OR LastName = ' ' THEN 1 ELSE 0 END) AS LastName ,State = SUM(CASE WHEN State IS NULL OR State = 'NULL' OR State = ' ' THEN 1 ELSE 0 END) AS State FROM Table.Customers WHERE DateOfRecord = '2023-05-01'   The output that I'd like to get: MaxRun FirstName LastName State 1 500 750 1000 ... View more