Unusual Oracle SUM

Question

I have two queries that, in my opinion, should give the same result, but they do not. Obviously, here I missed some important point, which I hope you can help me.

• The request (I assume this is incorrect):

  SELECT SUM(a.amount) AS A_SUM ---10 727.470 FROM billdetail a INNER JOIN bill c ON (a.bill_id = c.bill_id) INNER JOIN dates d ON (c.date_id = d.date_id) WHERE d.year = '2014' AND c.status <> 'D' AND a.status <> 'D'; 

• Request:

   SELECT SUM(C_SUM) ---10 754.279 FROM ( SELECT SUM(a.amount) AS C_SUM FROM billdetail a INNER JOIN bill c ON (a.bill_id = c.bill_id) INNER JOIN dates d ON (c.date_id = d.date_id) WHERE d.year = '2014' AND c.status <> 'D' AND a.status <> 'D' GROUP BY c.bill_id ); 

As you can see, query 1 gives me 10,727,470, where query 2 gives me 10,754.279, so 27 less.

Can you explain to me why this is so? I thought I was doing the same with both: select only the accounts from 2014, and then take all their accounts from there and summarize the amounts. But obviously, I have some kind of understanding problem, I hope you can help me.

Answer 1

You may click "Error 4604970" Invalid results with hash aggregation enabled. You can avoid this error by running alter session set "_gby_hash_aggregation_enabled"=false; . Here is a simple test case for an error that, unfortunately, still exists after all these years.

 SQL> select stddev(test), count(distinct test) from 2 ( 3 select 7/9 test from dual 4 union all 5 select 7/9 test from dual 6 ); select stddev(test), count(distinct test) from * ERROR at line 1: ORA-01428: argument '-.00000000000000000000000000000000000001' is out of range SQL> alter session set "_gby_hash_aggregation_enabled"=false; Session altered. SQL> select stddev(test), count(distinct test) from 2 ( 3 select 7/9 test from dual 4 union all 5 select 7/9 test from dual 6 ); STDDEV(TEST) COUNT(DISTINCTTEST) ------------ ------------------- 0 1