"#1054 - Unknown column" error when alias is used with-in the query?
While using 开发者_如何学Pythonthe following query, I get the error
#1054 - Unknown column 'plus' in 'field list'
when plus-minus
is used. Otherwise, query runs fine. I guess there is something related to Aliases which I do not know how to use. Please guide!
Thanks.
Query:
SELECT users.name,
count(*) as total,
SUM(sms.views)+ SUM(sms.downloads)+ (SELECT count(*) FROM `smsfb` WHERE (`feedback`=1 OR `feedback`=100) AND userid=users.uniqueID) AS plus,
SUM(sms.delreq)+(SELECT count(*) FROM `smsfb` WHERE (`feedback`=5 OR `feedback`=6) AND userid=users.uniqueID) AS minus,
plus-minus
FROM sms,users
WHERE sms.deviceID=users.uniqueID AND sms.catid!=23 AND sms.catid!=44 AND sms.catid!=45
AND date>="2011-10-03" AND date<"2011-10-09"
GROUP BY users.uniqueID HAVING total>10 ORDER BY total DESC LIMIT 0, 10
You can't use the alias of a column inside the select part of the query.
You could do it in this way:
SELECT name
, total
, plus
, minus
, plus - minus
FROM (
SELECT users.name,
count(*) as total,
SUM(sms.views)+ SUM(sms.downloads)+ (SELECT count(*) FROM `smsfb` WHERE (`feedback`=1 OR `feedback`=100) AND userid=users.uniqueID) AS plus,
SUM(sms.delreq)+(SELECT count(*) FROM `smsfb` WHERE (`feedback`=5 OR `feedback`=6) AND userid=users.uniqueID) AS minus
FROM sms,users
WHERE sms.deviceID=users.uniqueID
AND sms.catid!=23 AND sms.catid!=44
AND sms.catid!=45
AND date>="2011-10-03" AND date<"2011-10-09"
GROUP BY users.uniqueID HAVING total>10 ORDER BY total DESC
LIMIT 0, 10
) plusAndMinus
From Problems with Column Aliases:
You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column
精彩评论