mysql using calculated column at where/having

Question

i have an question how to use an "alias" column at the where/having clause. I know there are some question with an related topic. But I search trough the web and on stackoverfolw and doesn't find an solution.

So i hope for help at this way..

The following statement works well:

SELECT PHRASE,COUNT(*) AS A 
    FROM my_statistics 
    WHERE TIMESTAMP>NOW()-INTERVAL 7 DAY
    GROUP BY PHRASE ORDER BY A DESC 
    LIMIT 16;

PHRASE and TIMESTAMPare columns at the table. The code selects the top 16 PHRASES, which are inserts by users on the last 7 days. There are also exist an column USER and so i like now to select the top 16 phrases which are inserted by more than one user. So i tried this:

SELECT PHRASE,COUNT(*) AS A, COUNT(DISTINCT(USER)) AS B
    FROM my_statistics
    WHERE TIMESTAMP>NOW()-INTERVAL 7 DAY
          AND B>1
    GROUP BY PHRASE ORDER BY A DESC 
    LIMIT 16;

On other questions on stackoverflow i fond the info, that i have to use HAVING

SELECT PHRASE,COUNT(*) AS A, COUNT(DISTINCT(USER)) AS B
    FROM my_statistics
    WHERE TIMESTAMP>NOW()-INTERVAL 7 DAY      
    GROUP BY PHRASE ORDER BY A DESC 
    HAVING B>1
    LIMIT 16;

But this returns an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING B>1 LIMIT 16' at line 5

I have no idea, how the right syntax could be. Hope for any kind of help here. Thank you!

Duplicate: http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error — Sammaye, Jun 28 '12 at 13:02
@Sammaye reading http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error ...does it men, it's not possible? — The Bndr, Jun 28 '12 at 13:04
Alias colums are not supported, yes; according to the docs for WHERE — Sammaye, Jun 28 '12 at 13:04
@Sammaye is there a way to solve that without an alias column? i read a lot about using `having`for that... — The Bndr, Jun 28 '12 at 13:06
Yea I think having can have it but I am not sure about with the Limit, have you tried @Romil's Query? — Sammaye, Jun 28 '12 at 13:08

score 2 · Accepted Answer · answered Jun 28 '12 at 13:03

2

Place the ORDER BY A DESC after Having clause.

SELECT phrase, 
       Count(*)                AS A, 
       Count(DISTINCT( user )) AS B 
FROM   my_statistics 
WHERE  timestamp > Now() - INTERVAL 7 day 
GROUP  BY phrase 
HAVING b > 1 
ORDER  BY a DESC 
LIMIT  16;

answered Jun 28 '12 at 13:03

Romil Kumar Jain

20,239
9
63
92

splitting group by and order by.. that's it!! i always used both together!! thank you! – The Bndr Jun 28 '12 at 13:09

mysql using calculated column at where/having

1 Answers1