query cost difference

[monkeymafia]

Hey

I have a simple
"CD" Table (ID, CD_Name, Cost)

it consists of 6 different cds.

I am trying to write a query that lists the name and cost of each cd. and i wish to add a column that compares the cost to the average cost. i.e difference between cost and AVG cost of all cds.

currently i have.


select cd_name, cost, avg(cost) - cost as AVG_Difference
from cd
group by cost, cd_name


but no luck.
any guidance would be appreciated

[JeffB]

Hi monkeymafia,

Try this SQL:
SELECT CD.ID, CD.cd_name, CD.cost, cost - TABLE_AVG.AVG_Difference AS AVG_Difference
FROM CD, (SELECT AVG(cost) as AVG_Difference FROM CD) AS TABLE_AVG;

This one work also, but I'm not sure if the AVG() is computed more than one time
SELECT cd_name, cost, cost - (SELECT AVG(cost) FROM CD) AS AVG_Difference
FROM CD;

So the first one should do the trick ;)

JeffB

[monkeymafia]

yes it works. thanks alot. i didnt even think about a sub query for this. thanks again!