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.