SEO

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

Hi Jesse,

Jesse wrote:
> 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?

Column aliases can't be referred to later in the select list as far as I
know. The only way I know to do this is simply duplicate the expressions:

COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT

Baron

> COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT

Duuuh. Why didn't I think of that. What is MySQL's issue with referring to
the variables ("As" assignments, whatever you want to call them)? I've had
issues like this in situations like this one, when trying to use them in the
ORDER BY clause, and other places.

Jesse

Jesse wrote:
>> COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT
>
> Duuuh. Why didn't I think of that. What is MySQL's issue with
> referring to the variables ("As" assignments, whatever you want to
> call them)? I've had issues like this in situations like this one,
> when trying to use them in the ORDER BY clause, and other places.

They simply don't 'exist' at that point in time. All of the rows are
read at one point in time (according to a WHERE clause if applicable),
then the expressions within the column lists are done, then grouping,
order by etc., and finally HAVING clauses are done (where one can use a
column alias, when not available within a WHERE clause).

However, you should be able to use them in an ORDER BY - as they do
exist at that point - so I'm not sure that issue would have been related
to this.

Best regards

Mark

--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification