Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"#1054 - Unknown column" error when alias is used with-in the query?

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
like image 525
BufferStack Avatar asked Oct 16 '11 14:10

BufferStack


People also ask

What do 2 quotation marks mean?

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).

What is the 1 quotation mark called?

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.

What is a quotation mark used for?

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.

What is double quotation called?

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".


1 Answers

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

like image 195
DavidEG Avatar answered Sep 23 '22 19:09

DavidEG