How to use an aliased column in a MySQL WHERE clause?

Question

I have a MySQL query like this:

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10;

I want to restrict the results to rows where Occurrences>0. This seems very simple to me, but I can't seem to make it work. No matter what I try, WHEREs or HAVINGs, whenever I try to add this restriction I get NO ROWS in return. I'm positive that there is data in my table that should be returned. Does anyone know how to accomplish what I'm doing?

---

I've tried this, but it still doesn't work. Any ideas why it still won't work?

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" HAVING SUM(...some SQL removed for brevety)>0 GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10;

Answer 1

According to the SQL standard, the column aliases are not available in the body of the query, which is a confounded nuisance. Given what you have tried, it sounds as though the same is true of MySQL (and some other DBMS). You'll probably have to repeat the expression in the ORDER BY clause.

Also, the GROUP BY clause should list every column that is in the *, not just the primary key (though, logically, specifying the primary key should be sufficient).

Answer 2

I believe aggregates should be in the HAVING clause. Though, admittedly, I'm not sure if aliases are accepted.

HAVING (Occurrences > 0)

HAVING (SUM(...some SQL removed for brevety) > 0)

Alternatively, you can use a sub-query:

SELECT *
FROM (
   -- your current query
) AS sub
WHERE (Occurences > 0)

Answer 3

I am not as familiar with MySQL as I am with SQL Server, but in T-SQL, you can't use aliases in GROUP BY clauses (I originally thought ORDER BY clauses as well, but that has since been shown to be incorrect). In this case, you want to filter based on the results of a GROUP BY, so I would use a HAVING clause as follows:

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences
FROM
    some_table AS q
WHERE
    criterion = 'value'
GROUP BY
    q.P_id
HAVING
    SUM(...some SQL removed for brevety) > 0
ORDER BY
    Occurrences DESC
LIMIT 10;

Answer 4

Ah, I've found the place for it.

The statement is now:

SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q
WHERE criterion="value" GROUP BY q.P_id HAVING SUM(...some SQL removed for brevety)>0 ORDER BY Occurrences DESC LIMIT 10;

It seems to be working fine with ORDER BY Occurrences,