While using the 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
Use double quotation marks (“”) around a direct quote. A direct quote is a word- for-word report of what someone else said or wrote. You use the exact words and punctuation of the original. Harriet Jacobs writes, “She sat down, quivering in every limb” (61).
Single quotation marks are also known as 'quote marks', 'quotes', 'speech marks' or 'inverted commas'. Use them to: show direct speech and the quoted work of other writers.
Quotation marks are most often used to mark something that is spoken or, in other words, to designate a direct quote. That is, they display something that's been said, word for word.
Quotation marks can be double ("...") or single ('...') - that is really a matter of style (but see below for more about this). Quotation marks are also called "quotes" or "inverted commas".
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With