HELP: Problem with MySQL column aliases

[theosib]

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.