Home » SQL & PL/SQL » SQL & PL/SQL » Getting wrong result on summing
Getting wrong result on summing [message #194504] Fri, 22 September 2006 07:35 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I am having a SQL query which is referring three tables and joined correctly.

Table A (is the Master table)

-------------
Trans_no Userid
----------------------
1 61
2 62

Table B (is the Child Table)

--------------

Trans_no Trans_dtl_no COST_AMT Userid
-------------------------------------------------
1 1 22.57 61
1 2 -22.57 61


Table C (Lookup table for Username)

Userid Username
------------------------------------------------
61 Tom
62 Terry


I am having the query like below.

Select A.Trans_no,B.Trans_dtl_ln_no,sum(B.COST_AMT) COST_TOTAL,C.Username
from A,B,C
where A.Trans_no=B.Trans_no
and C.Userid=B.Userid and C.Userid=A.Userid
group by A.Trans_no,A.Trans_dtl_ln_no


This is returning the result like below.

Trans_no Trans_dtl_ln_no COST_TOTAL Username
---------------------------------------------------------------
1 1 225.7 Tom
1 2 -225.7 Tom


Please see the COST_TOTAL is increasing by 1 decimal place, what may be the reason for this, this happens only when all the three tables are included in the join, but when only TABLE A and TABLE B is joined and selected from this does not happen.


Anyone can help me in this issue ?

Thanks,

Nirmal Narayanan.


icon2.gif  Re: Getting wrong result on summing [message #194507 is a reply to message #194504] Fri, 22 September 2006 07:52 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Select A.Trans_no,B.Trans_dtl_ln_no,sum(abs(B.COST_AMT)) COST_TOTAL,C.Username
from A,B,C
where A.Trans_no=B.Trans_no
and C.Userid=B.Userid and C.Userid=A.Userid
group by A.Trans_no,A.Trans_dtl_ln_no
icon6.gif  Re: Getting wrong result on summing [message #194635 is a reply to message #194507] Sat, 23 September 2006 04:50 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

i have found one problem in this query

Select A.Trans_no,B.Trans_dtl_ln_no,sum(abs(B.COST_AMT)) COST_TOTAL,C.Username
from A,B,C
where A.Trans_no=B.Trans_no
and C.Userid=B.Userid and C.Userid=A.Userid
group by A.Trans_no,A.Trans_dtl_ln_no

--------------------------------------------------
you have to use c.username in group by expression
then your problem will be solved

check the following query it will apply

................................


select a.trans_no,b.trans_dtl_no,sum(b.cost_amt),c.user_name
from trans_m a,trans_detail b,users c
where A.Trans_no=B.Trans_no
and C.User_id=B.User_id and C.User_id=A.User_id
group by A.Trans_no,b.Trans_dtl_no,c.user_name


..................................................

if you want to see the sum of both the fields then

remove field b.trans_dtl_In_no from select statement
and
also from group by expression


your result will be = 0

[Updated on: Sat, 23 September 2006 05:08]

Report message to a moderator

Re: Getting wrong result on summing [message #195003 is a reply to message #194635] Tue, 26 September 2006 08:24 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I am having a problem with the following query, the problem is described after the query.

--------------------------------------------------------------------------------------------------------------------------

select distinct
GP_IA_TRANSACTION_HEADER.TRANS_NO,
GP_IA_TRANSACTION_detail.TRANS_DTL_LN_NO,
GP_IA_TRANSACTION_HEADER.LAST_UPDATE_DTS,
GP_GP_USER_ROLES.USERNAME,
sum(GP_IA_TRANSACTION_DETAIL.UNIT_COST_AMT) UNIT_COST,
sum(GP_IA_TRANSACTION_DETAIL.UNIT_RTL_AMT) UNIT_RTL,
sum(GP_IA_TRANSACTION_DETAIL.EXT_COST_AMT) EXT_COST,
sum(GP_IA_TRANSACTION_DETAIL.EXT_RTL_AMT) EXT_RTL
from
GP_IA_TRANSACTION_DETAIL,
GP_IA_TRANSACTION_HEADER,GP_GP_USER_ROLES
where GP_IA_TRANSACTION_HEADER.TRANS_NO=GP_IA_TRANSACTION_DETAIL.TRANS_NO
and to_char(GP_GP_USER_ROLES.USER_ID)=GP_IA_TRANSACTION_HEADER.trans_src_id
group by rollup(GP_IA_TRANSACTION_HEADER.TRANS_NO,
(GP_IA_TRANSACTION_HEADER.TRANS_NO,GP_IA_TRANSACTION_detail.TRANS_DTL_LN_NO,GP_IA_TRANSACTION_HEADER.LAST_UPDATE_DTS,
GP_GP_USER_ROLES.USERNAME))
order by GP_IA_TRANSACTION_HEADER.TRANS_NO

-------------------------------------------------------------------------------------------------------------
Result for the above query is given below.

For Eg. below against Transaction Number=1161 we have two rows in
GP_IA_TRANSACTION_DETAIL i.e for TRANS_DTL_LN_NO=1 and TRANS_DTL_LN_NO=2
and the value in EXT_COST_AMT in GP_IA_TRANSACTION_DETAIL against these rows are
-22.71 and 22.71, but the query is returning -227.1 and 227.1 against these values this is happening
when we sum the values and join against table GP_GP_USER_ROLES for getting the username.


This means that it is decreasing by 1 decimal place to the value if the value is 30 it is
returning as 300 and if the value is 32 it is showing 320 (against TRANS_NO=1260 below)

But this does'nt seem to be a cartesian product with GP_GP_USER_ROLES,as the query is returning
the correct number of rows. (1161 has got two rows in GP_IA_TRANSACTION_DETAIL and the following query returns the
rows correctly and the subtotal row (since this is a rollup) for the value = 1161)

I am investigating on this issue, but would appreciate if you find anything wrong with the query, after
testing on your side.

------------------------------------------------------------------------------------------------------------
SQL> /

TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1161 1 20-SEP-06 GP
-227.1 0

1161 2 20-SEP-06 GP
227.1 0

1161
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1260 1 20-SEP-06 GP
-320 0

1260 2 20-SEP-06 GP
320 0

1260
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1342 1 20-SEP-06 GP
-790 0

1342 2 20-SEP-06 GP
790 0

1342
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1343 1 20-SEP-06 GP
-300 0

1343
-300 0

1344 1 20-SEP-06 GP
-2500 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1344
-2500 0

1345 1 21-SEP-06 GP
-340 0

1345 2 21-SEP-06 GP
340 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1345
0 0

1346 1 21-SEP-06 GP
-300 0

1346
-300 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1353 1 22-SEP-06 GP
-780 0

1353
-780 0

1354 1 22-SEP-06 GP
-170 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1354
-170 0

1355 1 22-SEP-06 GP
-130 0

1355
-130 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1356 1 22-SEP-06 GP
-130 0

1356
-130 0


-4310 0


27 rows selected.

[Updated on: Tue, 26 September 2006 08:25]

Report message to a moderator

Re: Getting wrong result on summing [message #195268 is a reply to message #195003] Wed, 27 September 2006 12:43 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Over 200 posts and you still don't know how to format your posts to make it easier for us to read?
Previous Topic: grouping data
Next Topic: Help with simple(??) query on two columns
Goto Forum:
  


Current Time: Mon Dec 05 05:03:38 CST 2016

Total time taken to generate the page: 0.08128 seconds