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)
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);