Mysql Error: #1247 - Reference 'karma' not supported (reference to group function)

Question

Here is my mysql query below. Through many helpful questions and comments I am almost at the end of my journey. The idea behind this query is a user submits a link, the application inserts two rows, one into links and another into votes (a default vote, why wouldn't a user vote for their own submission?) Then every vote is just another row in the votes table with a either a karma_up or karma_down equaling 1 (soon to be changed to karma_delta to save on the extra column. I also have the popularity algorithm in there which seems to be b0rking my query. Running the below query warrants me this error.

#1247 - Reference 'karma' not supported (reference to group function)

The whole point of the majority of this query is to get the karma

SELECT links.*, (SUM(votes.karma_up) - SUM(votes.karma_down)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
ORDER BY (karma - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC
LIMIT 0, 100

Without the popularity algorithm at the ORDER BY part the query runs perfectly, adding the sum'ed up karma from the votes table and tacking on an extra column with it's value.

Answer 1

The problem is here:

`ORDER BY karma...

You can't order by something which is defined as an alias. Try this:

`ORDER BY ((SUM(votes.karma_up) - SUM(votes.karma_down)) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC`

Hopefully the DB can figure out not to evaluate it twice. If not, use an inner select without the order by first to create the alias, then use another select to order.