Opinion on Best Way To Implement New Feature

[jrotunda85]

So I am in the process of putting some finishing touches on a new section of my website which allows users to keep track of their cigars in an online inventory (or humidor) type system. I'm kind of stuck; however, on what the best way to capture and process data when being displayed. So right now, a user adds a record for a particular cigar, a new row is created with information relevant to that entry (price per cigar, date purchased, quantity purchased, and the particular cigar purchased). So what ends up happening is let's say I purchased the same cigar on 3 different dates for 3 different amounts, I would have 3 different, unique entries.

When a user visits their page which lists this inventory, I want all the data combined based on CigarID (which identifies the unique cigar) and I need it to SUM the quantities and compute an average; however, the average needs to be based on the quantity not the number in the price paid field. to give you an example, let's say I add the following three cigars -

[B]CigarID | Quantity | Price Paid (Per Cigar) | Date Purchased[/B]
1 | 10 | 1.00 | 06/01/2011
1 | 20 | 2.00 | 06/02/2011
1 | 30 | 3.00 | 06/03/2011

So, based on this data set, if I was to use the AVG function, it would return a value of $2.00 (or (1+2+3) / 3). However, the real average, accounting for the quantity should be more like this --

(10 * 1.00) + (20 * 2.00) + (30 * 3.00)
----------------------------------
SUM(Quantity)

This would equal to $1.73. What's the best way to calculate this? I'm assuming I'd have to do some type of loop to grab the data, I'm currently using the following SQL but it's not returning the correct average value --

$SQL = "SELECT a.*, b.CName, c.Description FROM humidor a
LEFT JOIN cigar b on a.CigarID = b.CigarID
LEFT JOIN cd_manufacturer c on b.Manufacturer = c.Code
WHERE UserID = $UserID AND DisplayCD = 1 AND Quantity > 0
GROUP BY CigarID, AVG(PricePaid), Count(Quantity)
ORDER BY c.Description, b.CName ASC";
$Result = mysql_query($SQL);

[vibhaJ]

What i understood, Your calculation is giving 2.33 not 1.73.

(10 * 1.00) + (20 * 2.00) + (30 * 3.00)
----------------------------------
SUM(Quantity)

=> 140 / 60
=> 2.33

Check this query.

SELECT sum( (
Quantity * Price
) ) , sum( Quantity ) , sum( (
Quantity * Price
) ) / sum( Quantity )
FROM `cigar`

[jrotunda85]

What i understood, Your calculation is giving 2.33 not 1.73.

(10 * 1.00) + (20 * 2.00) + (30 * 3.00)
----------------------------------
SUM(Quantity)

=> 140 / 60
=> 2.33

Check this query.

SELECT sum( (
Quantity * Price
) ) , sum( Quantity ) , sum( (
Quantity * Price
) ) / sum( Quantity )
FROM `cigar`

Whoops, good catch. Perhaps I should try to do math at this time of night! :|

Does this account for the fact that some cigars might have 1, 2, 3, or some other number entries?

[vibhaJ]

It will give average of all individual cigars, no matters how may entries.
I forget to group by CigarId.

SELECT CigarId , sum( (
Quantity * Price
) ) / sum( Quantity ) as avg
FROM `cigar`
group by CigarId