Jump to content

theosib

New Members
  • Posts

    1
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

theosib's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.