Problem with columns

Anyone know how to get the sum of a column created by a SELECt statement?

Basically I have something like this :

A group of people rent from a house per week, if the group is too big for one house they will be split across multiple homes, this is the SELECT query I have so far :

SELECT GroupID, HouseID, PricePerWeekPerPerson, LengthOfStay, NumberOfPeopple, PricePerWeek * (LengthOfStay * NumberOfPeople) HouseTotalCost

This outputs :

1, 1, 20, 2, 3, 120
1, 2, 25, 2, 4, 200

now I want a GroupTotal which calculates the sum of all the HouseTotalCost's yet if I try : SUM(HouseTotalCost) MySQL says 'Unkown column'


Any ideas?

Also is it possibel with SQL to have the GroupTotal not repeated per row (per house) ? if not it is no biggie


Thanks in advance

 

http://www.google.com/search?q=sele...s=org.mozilla:en-US:official&client=firefox-a

 

As I said I have tried SUM(HousetotalCost) however MySQL says 'unkown column not found in field list', I'm guessing thats because the column is being created by the SELECT statement (it isnt an actual field in the table)

 

Then maybe try doing a sub select:

SELECT SUM(HouseTotalCost) FROM (
SELECT GroupID, HouseID, PricePerWeekPerPerson, LengthOfStay,
NumberOfPeopple, PricePerWeek * (LengthOfStay * NumberOfPeople) AS
HouseTotalCost
)

 

Ok maybe I should of posted my whole SQL, here it is

SELECT groups.GroupID, GroupSize, LengthOfStay, groupaccommodation.AccommodationNo, RoomsFilled, PPW, PPW * ( RoomsFilled * LengthOfStay ) HouseTotalCost, PPW *
FROM groups, accommodation, groupaccommodation
WHERE groups.GroupID = groupaccommodation.GroupID
AND accommodation.AccommodationNo = groupaccommodation.AccommodationNo

Now if I enclose that statement in the
SELECT SUM(HouseTotalCost) FROM (
) AS
HouseTotalCost
)

liek you suggested MySQL cries, and not in a helpful way

Thanks for trying to help!

 

perhaps i am placing it wrong, but I think i have tried most variations?