The following query works just fine:
SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity
FROM Buses B
LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS
CampCount ON CampCount.BusID=B.ID
LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID)
AS CounselorCount On CounselorCount.BusID=B.ID
ORDER BY B.BusNum
However, I would like to add the to have a total of the adults and kids on
the bus, so I change my query as follows:
SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity,
COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity,
Kids + Adults As GT
FROM Buses B
LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS
CampCount ON CampCount.BusID=B.ID
LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID)
AS CounselorCount On CounselorCount.BusID=B.ID
ORDER BY B.BusNum
When I try to execute this, I get the error, "Unknown column 'Kids' in
'field list'" How do I properly add these together to get a total column?
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
