Home » SQL & PL/SQL » SQL & PL/SQL » Incorrect Summation
Incorrect Summation [message #243902] Sun, 10 June 2007 23:23 Go to next message
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
Re: Incorrect Summation [message #243942 is a reply to message #243902] Mon, 11 June 2007 01:32 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and apply How to format your posts.

Regards
Michel
Previous Topic: Bulk Inserts using existing functions
Next Topic: Strange Error on Creating DB Table!!!
Goto Forum:
  


Current Time: Sat Dec 10 11:00:38 CST 2016

Total time taken to generate the page: 0.08881 seconds