I am using version 3.23.41 of MySQL, and I\'m having a problem getting correct (expected?) behavior with column aliases.
I have a database A which has, among other things, an id field, something like this:
CREATE TABLE a {
somedata VARCHAR(10),
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
};
I have a second table which records ratings of entries in the first:
CREATE TABLE b {
id INT UNSIGNED NOT NULL,
userid INT UNSIGNED NOT NULL,
plus TINYINT DEFAULT NULL,
minus TINYINT DEFAULT NULL,
PRIMARY KEY (id, userid)
};
There are reasons why I want to track the positive and negative votes separately. Anyhow, here is a query that works and gives me the information I want:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, COUNT(plus)-COUNT(minus) AS rating FROM a LEFT OUTER JOIN b ON
(a.id=b.id) GROUP BY a.id ORDER BY rating;
This works fine, but it seems to me that having COUNT(plus) twice and COUNT(minus) twice may not be noticed and optimized by the database. My original query was this:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, FROM a LEFT OUTER JOIN b ON (a.id=b.id) GROUP BY a.id ORDER BY (pl-mi);
From that, I get the following error:
ERROR 1054: Unknown column \'pl\' in \'order clause\'
Now, before, when I used to have only \'pl\' and no \'mi\', it worked great. I could use \'ORDER BY pl\', and it would just work. Why can\'t I use \'(pl-mi)\'?
Next, I tried this query instead:
SELECT somedata, a.id, COUNT(plus) AS pl, COUNT(minus) AS mi, (pl-mi) AS rating FROM a LEFT OUTER JOIN b ON (a.id=b.id) GROUP BY a.id ORDER BY rating;
From that, I get this error:
ERROR 1054: Unknown column \'pl\' in \'field list\'
So my questions are:
- Is it going to be wasteful of time to have COUNT(plus) and COUNT(minus) twice?
- Why can I use the alias in an ORDER BY clause by itself but not in an expression?
- Why can\'t I use the alias in the SELECT after it\'s been created?
Thanks very much for your help.