Solved

Rounding issues

Posted on 2013-06-20
17
212 Views
Last Modified: 2013-07-01
Can someone help me out, the below results when I add up the sum of qty*price,

I get, 345.0036,

my header table tells me it's suppose to 345.01

anyone know what's going on?

thanks

QTY  Price
6	2.05
24	1.7898
12	2.18
12	1.19
12	1.19
8	2.39
8	2.39
8	2.39
24	0.34
24	0.42
6	1.7267
6	1.6542
12	1.542
12	2.2665
24	1.4018
12	1.2802
6	1.2779
12	1.974
12	1.09

Open in new window

0
Comment
Question by:FutureDBA-
  • 8
  • 7
  • 2
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39263569
>>I get, 345.0036,

Given that data, that's what I get as well.

>>my header table tells me it's suppose to 345.01

What header table?

I'm afraid we need more information.
0
 

Author Comment

by:FutureDBA-
ID: 39263615
this data is coming from 2 order tables, header and detail.

our ERP does calculations and on the detail to place the total value in a field on the header.

the detail however, does not match what is generated on the header..

the new data I am generating to place in a new table for EDI purposes must match, so the sum of line items price*qty must match the total value for that order

when I do this, I am getting 345.0036,

our ERP calculates 345.01
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39263631
>>our ERP does calculations and on the detail to place the total value in a field on the header.

Then to be accurate 100% of the time you need to get the same calculation the app does.

You can try and 'guess' and get it right for this set of data but it may not be accurate moving forward.

Just out of curiosity, your system keeps prices in fractional cents?  I don't think I've ever seen prices of items past two decimal places.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39263643
For example, I can get 345.01 with the query below but I seriously doubt this is accurate for any other data set:

select trunc(sum(qty*round(price,3)),2) from tab1;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39263650
If they take any fractional cents and round up to the next cent, this one might be accurate for any data set but you will need to test, test, test:

select ceil(sum(qty*price)*100)/100 from tab1;
0
 

Author Comment

by:FutureDBA-
ID: 39263686
some of our item pricing are government regulated and the fractions of up to 5 decimal places amount to 1000's of $ earned or lost.

I am going to check with your query now
0
 

Author Comment

by:FutureDBA-
ID: 39263711
I guess the main thing I need, is the correct pricing of each individual item, that will give me 345.01

from my understanding, what's being placed in the header is being rounded on the results of  qty*price

I am looking for the value of price before, so that when i do qty*price it gives me the 345.01,

the 345.01 is not a result of the query as it is already in the header.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39263730
>>what's being placed in the header is being rounded on the results of  qty*price

Didn't try that...  seems to also generate the correct value:
select sum(round(qty*price,2)) from tab1;

>>the 345.01 is not a result of the query as it is already in the header.

I understand the value is fixed in another place.  It was my understanding that you wanted a query to reproduce that number.  If this isn't what you are after, please clarify.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 39263772
Another possibility -
select sum(round(totamt,2)) from
(select qty*price totamt
from table1);
0
 

Author Comment

by:FutureDBA-
ID: 39263808
The query I am after will give me the correct item price amount to get to the 345.01

that my header is giving me
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39263838
>>The query I am after will give me the correct item price amount to get to the 345.01


OK, I've given you two that do that (three but the first one I know is bad... it was just an example).

awking00 posted another that likely gives you the correct number (I didn't test it but I trust him).

The catch here is there is no way to ensure it will ALWAYS provide the correct number for any set of data.

To guarantee this, you need the exact formula the ERP app uses to calculate the header record.
0
 

Author Comment

by:FutureDBA-
ID: 39263944
sum(round(Q.PRICE,2)/Q.QUAINV) worked across a days worth of data, down to the cent,

going to run it across a month, if this works, it's exactly what i was looking for, i will report my findings shortly.

thanks.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39264068
>>sum(round(Q.PRICE,2)/Q.QUAINV) worked across a days worth of data, down to the cent<<
Did you mean
sum(round(Q.PRICE,2)*Q.QUAINV) worked across a days worth of data, down to the cent?
Even so, that does not compute to 345.01
SQL> select * from table1;

       QTY      PRICE
---------- ----------
         6       2.05
        24     1.7898
        12       2.18
        12       1.19
        12       1.19
         8       2.39
         8       2.39
         8       2.39
        24        .34
        24        .42
         6     1.7267
         6     1.6542
        12      1.542
        12     2.2665
        24     1.4018
        12     1.2802
         6     1.2779
        12      1.974
        12       1.09
SQL> select sum(round(price,2)*qty)
  2  from table1;

SUM(ROUND(PRICE,2)*QTY)
-----------------------
                 344.94

slightwv, thanks for the trustworthiness. It does, in fact, provide the anticipated result.
SQL> select sum(round(totamt,2)) from
  2  (select qty*price totamt
  3  from table1);

SUM(ROUND(TOTAMT,2))
--------------------
              345.01
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39264074
I figured it did since it was basically the same as the one I posted in http:#a39263730 just using an inline view.  ;)
0
 

Author Comment

by:FutureDBA-
ID: 39264105
awking01, what i stated is correct, i had to divide by the QTY to get the price i was looking for.

WITH Q AS (
SELECT 
  SLDTCKNUM AS TICKET,
  SLDTRNQTY AS QUAINV,
  'EA' AS QUAMC,
  SLDTRNPRC AS UNITP,
  SUBSTR(ITMUPCNUM, 1,12) AS PSIDUI,
  ITMSHTDES AS DESCR,
  (SLDTRNQTY*SLDTRNPRC) AS PRICE
FROM wagrmsldp, rmitmp@ermsd
where slditmnum = itmitmnum
  and slddeldte = 20130611
  and sldtrnqty<>0)

select p810_header.idin, Q.QUAINV, Q.QUAMC, Q.UNITP, sum(round(Q.PRICE,2)/Q.QUAINV) AS PRICE, Q.PSIDUI, Q.DESCR from q, p810_header where INVN = TICKET
group by p810_header.idin, Q.QUAINV, Q.QUAMC, Q.UNITP, Q.PSIDUI, Q.DESCR;

Open in new window



QTY		ERP_PRICE	EXPERTEXCHANGE_PRICE	ERP TOTAL	EXPERTEXCHANGE_TOTAL
----------------------------------------------------------------------------------------
6		1.2779		1.278333333		7.6674		7.67
6		1.6542		1.655			9.9252		9.93
6		1.7267		1.726666667		10.3602		10.36
6		2.05		2.05			12.3		12.3
8		2.39		2.39			19.12		19.12
8		2.39		2.39			19.12		19.12
8		2.39		2.39			19.12		19.12
12		1.09		1.09			13.08		13.08
12		1.19		1.19			14.28		14.28
12		1.19		1.19			14.28		14.28
12		1.2802		1.28			15.3624		15.36
12		1.542		1.541666667		18.504		18.5
12		1.974		1.974166667		23.688		23.69
12		2.18		2.18			26.16		26.16
12		2.2665		2.266666667		27.198		27.2
24		0.34		0.34			8.16		8.16
24		0.42		0.42			10.08		10.08
24		1.4018		1.401666667		33.6432		33.64
24		1.7898		1.79			42.9552		42.96
							
							345.0036	345.01

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39264127
>> i had to divide by the QTY to get the price

You didn't say it was a computed field.  You had to divide because you were already doing (SLDTRNQTY*SLDTRNPRC) in the WITH query.

It is the same math both awking00 and myself were doing.
0
 

Author Comment

by:FutureDBA-
ID: 39264321
i apologize for any confusion i may have caused.

will award points shortly.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now