calculate cost?

[nikko50]

Hi there🙂
OK, I have an equipment table. In this table there is an onhand, receivedqty, and totalcost fields. How can I query to get an average cost and extended cost?
The math would be totalcost/receivedqty=averagecost and the extended cost would averagecost*onhand. Is it possible to do the equations within the mysql statement. I tried the below but it won't let me use average to get the extended cost.

SELECT tcost, tqty, tcost / tqty AS 'average' , average*tonhand AS 'extended'
FROM equipment

[nikko50]

UPDATE. I think I got it to work with the below statement. How can I format the extended and average to show only 2 decimal places within the statement. Is this possible?

SELECT tcost, tqty, tcost / tqty AS 'average', ( ( tcost / tqty ) * tonhand ) AS 'extended'
FROM equipment

[rincewind456]

You may want to look at these GROUP BY (Aggregate) Functions

[toxic_brain]

use the round() function

SELECT round( tcost / tqty,2) AS avg ...