MySQL variable/alias not usable

Question

So what I'm trying to do at the moment is to select a dataset of values depending on another autocalculated value.(Sounds pretty complicated. It isn't I think and I'm not that much into english)

My SQL code looks like this:

SELECT ticket.ref_id AS id, COUNT( * ) AS reports, (COUNT(*)/post.views) AS
treshold,post.views AS views
FROM ticket, post
WHERE ticket.ref_id = post.id AND (reports/post.views)
GROUP BY ref_id
ORDER BY views,reports DESC

The problem is, when I try to use treshold or reports in the WHERE clause the query fails and says that there is no such value in 'field list'. I know where the problem is, it's that MySQL seems not to allow the use of 'self defined' fields in the WHERE clause but i really need this and I dont know how to fix it.
Just using COUNT(*) doesn't work either because it doesn't know what to count.

Can anyone help me on this problem? I think it's totally basic SQL but I'm stuck on this at the moment.

raveren · Accepted Answer · 2011-05-10 19:35:43Z

2

You can use the the HAVING clause instead of WHERE for the unreferencable columns. It's less performant, but it will do the job.

http://dev.mysql.com/doc/refman/5.0/en/select.html

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions

answered May 10, 2011 at 19:35

raveren

18.8k12 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

awildturtok Over a year ago

thank you :) I knew it all along that this problem was simpler as i made it

Collectives™ on Stack Overflow

MySQL variable/alias not usable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related