SourceForge has been redesigned. Learn more.
Close

#46 Mantis ID:8321 - Summarized Sales By Customer By Item - Average Price Calculation is Wrong

open
nobody
None
3
2009-01-30
2009-01-30
omfgppc
No

Reporter: xikar
Severity: Patch
Category: Sales
Project: xTupleApps

Version: 3.2.0

Description:
The "Ave Price" column in the "Summarized Sales By Customer By Item" report uses the aggregate AVG() function.
"An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows."

baseunitprice | cohist_qtyshipped | baseextprice
---------------+-------------------+--------------------
2.00 | 10 | 20.00
8.00 | 1 | 8.00

AVE(baseunitprice) = 5.00 <- Incorrect Average Price
(SUM(baseextprice) / SUM(cohist_qtyshipped)) = 2.5454 <- Real Average Price

http://postbooks.svn.sourceforge.net/viewvc/postbooks/xtuple/trunk/guiclient/dspSummarizedSalesByCustomerByItem.cpp?revision=5078&view=markup

Line 122:
- "AVG(baseunitprice) AS avgprice, SUM(cohist_qtyshipped) AS totalunits,"

+ "CASE WHEN SUM(baseextprice) = 0"
+ " THEN 0"
+ " ELSE (SUM(baseextprice)/SUM(cohist_qtyshipped))"
+ " END AS avgprice,"
+ "SUM(cohist_qtyshipped) AS totalunits,"

Steps to reproduce:

Additional information:

Read more at http://www.xtuple.org/mantis/view.php?id=8321

Discussion


Log in to post a comment.