[RESOLVED] SUM Problem

cristal777

Hi,

I am having a sum problem.

When I try and SUM two columns together I get an error, see my code below:

SELECT ID
, sum( IF (Cards.Card = 'Yellow', 1, 0) ) AS "YellowCard"
, sum( IF (Cards.Card = 'Red', 1, 0) ) AS "RedCard"
, sum( YellowCard ) + sum( RedCard ) AS "Total"
FROM Cards

The following error:

#1054 - Unknown column 'YellowCard' in 'field list'

Any suggestions will be greatly appreciated.

Thanks,

bradgrafelman

Try using backticks (`) instead, or don't even quote the aliases since they don't consist of reserved words or special characters.

cristal777

bradgrafelman wrote:
Try using backticks (`) instead, or don't even quote the aliases since they don't consist of reserved words or special characters.

Thanks for you help and quick reply bradgrafelman.

Unfortunately I tried both backticks and no quotes on the aliases but still the same error:

#1054 - Unknown column 'YellowCard' in 'field list'

bradgrafelman

Oops, I didn't even read the last sum() statement.

The problem is that you can't use column aliases in the SELECT column list. I would say the easiest solution would be to simply calculate this total in PHP ($row['YellowCard'] + $row['RedCard']).

cristal777

bradgrafelman wrote:
Oops, I didn't even read the last sum() statement.

The problem is that you can't use column aliases in the SELECT column list. I would say the easiest solution would be to simply calculate this total in PHP ($row['YellowCard'] + $row['RedCard']).

Cool, thanks Brad.