Using mysql sum to multiple units with costs so it gives the average cost per unit when grouped by another column

Databases
#1

Hi,

I am using php to connected and pull data from my database, I have the following

SELECT SUM(p_units * p_cost) AS pcosts FROM inventory_log, supstockmerge WHERE m_id=supstockmerge.id AND date=‘$date1’ group by t_code

Now I have table like

Units | Cost | tcode
3 | 0.34 | 34343
9 | 0.24 | 34343
3 | 0.23 | 3399

Now my select statement from the database groups them by tcode as I want it to, but the results it brings up is incorrect, rather than taking the units and multiplying by the cost for each row and then once it’s found all the figures, it should add them all together to give the overall average price per unit and divide that total price for each tcode by the number of summed units for each grouped tcode, but
it seems to give figures that are slightly incorrect.

For example I want it so the result for tcode: 34343 is 12 units with average price of £0.265

Is there a better way to do the select statement or using php to get the figures correct.

Thanks

#2

I’ve moved this to the SQL section as I’m certain this can be done inside the query.

#3

You’re calculating the total cost. If you want total units and average cost, you just need to tweak the query.

SELECT SUM(p_units * p_cost) AS totalCost
     , SUM(p_units) AS totalUnits
     , AVG(p_cost) AS averageCost 
  FROM inventory_log 
 INNER JOIN supstockmerge ON m_id = supstockmerge.id
 WHERE date=‘$date1’ 
 GROUP BY t_code
#5

Hi,

Thanks, its done what I needed.