OraFAQ Forum: SQL & PL/SQL » Incorrect Summation

Home » SQL & PL/SQL » SQL & PL/SQL » Incorrect Summation

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Incorrect Summation [message #243902]

Sun, 10 June 2007 23:23

ar_shriks
Messages: 2
Registered: September 2006

Junior Member

We have a table (say tab1) in which the data looks as follows. This table has about 5 millions rows each for each margin_code and for each IAN there can be upto 100 margin_codes spread over various cost categories.

Sample data
===========
IAN ,COST_CATEGORY,MARGIN_CODE, MARGIN_VALUE
------------,-------------,-----------,-------------------
AL20114102 , 1, 1, 24.60
AL20114102 , 1, 2, 111.07
AL20114106 , 1, 2, 191.53
AL20114107 , 1, 1, 163.70
AL20114107 , 1, 2, 256.50
AL20114108 , 1, 1, 21.50
AL20114108 , 1, 2, 108.92
AL20114109 , 1, 1, 234.90
AL20114109 , 1, 2, 241.48
AL20114113 , 1, 1, 58.80
AL20114113 , 1, 2, 63.26
AL20114114 , 1, 2, 225.95
AL20114118 , 1, 1, 114.50
AL20114118 , 1, 2, 84.03
AL20114119 , 1, 1, 69.90
AL20114119 , 1, 2, 71.12

I wrote the following SQL to insert a summation into the same table.

INSERT INTO tab1
SELECT ian, cost_category, 3 as margin_code, sum(nvl(margin_value, 0))
FROM tab1
WHERE margin_code IN (1, 2)
GROUP BY ian, cost_category
/

My problem is that after inserting, I find that the sum(margin_value) where margin_code = 3 is not equal to sum(margin_value) where margin_code in (1,2).

When I test this against a small sample of data there is no problem, but when I run the SQL against the production table, the summation does'nt match.

Can you please let me know what can be wrong here and what am I missing?

Thanks a lot in advance.

Regards
Shrikanth

Report message to a moderator