CODEWITHSUNDEEP
mysql
jawns317
jawns317

Reputation: 1746

MySQL: Order by absolute value of user-defined columns

Here's a stripped-down version of a query I'm trying to use:

SELECT user_id,
(SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 AND pref = 1) /
(SELECT COUNT(*) FROM responses WHERE poll_id = 1 AND pref = 1) AS pref_percent,
(SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 ) /
(SELECT COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent,
ABS(all_percent - pref_percent) AS deviation
FROM responses
GROUP BY user_id
ORDER BY deviation DESC

I get this error: Unknown column 'all_percent' in 'field list'

Apparently, I can't reference a user-defined column ("all_percent") when constructing another user-defined column ("deviation").

So, my question is ... how might I modify this query to avoid the error?

I'd really like to have the query results sorted by deviation, rather than having to do that work in PHP.

Upvotes: 0

Views: 1273

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Wrap an outer query around your current query:

SELECT user_id, pref_percent, all_percent, ABS(all_percent - pref_percent) AS deviation
    FROM (SELECT user_id,
          (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 AND pref = 1) /
          (SELECT COUNT(*) FROM responses WHERE poll_id = 1 AND pref = 1) AS pref_percent,
          (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 ) /
          (SELECT COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent
          FROM responses
          GROUP BY user_id
         ) t
    ORDER BY deviation DESC

Upvotes: 2

Jai
Jai

Reputation: 3609

You need to use a variable @var:=

for eg..

...SELECT @var := COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent, ABS(@var1 - @var2) AS deviation...

Upvotes: 1

Pentium10
Pentium10

Reputation: 207982

SELECT * 
FROM   (SELECT user_id, 
               (SELECT COUNT(1) 
                FROM   responses 
                WHERE  option_id = 1 
                       AND poll_id = 1 
                       AND pref = 1) / (SELECT COUNT(1) 
                                        FROM   responses 
                                        WHERE  poll_id = 1 
                                               AND pref = 1)   AS pref_percent, 
               (SELECT COUNT(1) 
                FROM   responses 
                WHERE  option_id = 1 
                       AND poll_id = 1) / (SELECT COUNT(1) 
                                           FROM   responses 
                                           WHERE  poll_id = 1) AS all_percent, 
               Abs(all_percent - pref_percent)                 AS deviation 
        FROM   responses 
        GROUP  BY user_id) t 
ORDER  BY t.deviation DESC

Upvotes: 0

Related Questions