MySQL - use a conditional with count?

I have the following sql statement:

select val, count(*) as tally from sometable group by val order by tally desc

Which produces the following (example) table:

val | tally
----+-------
4   | 20
5   | 10
6   | 5
7   | 3
8   | 2

Now, I want to only display the rows w开发者_如何转开发here tally > 5, so the result will be:

val | tally
----+-------
4   | 20
5   | 10

I tried this statement, but it does not work (it says "tally" is unknown):

select val, count(*) as tally from sometable where tally > 5 group by val order by tally desc

Try HAVING (more information):

select val, count(*) as tally from sometable group by val having tally > 5 order by tally desc

There are two approaches you can take:

1: Use HAVING. This means that the query results will be filtered as per the HAVING clause and only some of the original result rows will be returned to you:

SELECT val, COUNT( * ) AS tally
FROM sometable
GROUP BY val
ORDER BY tally DESC
HAVING tally > 5

2: Use a sub-select. This may give you better performance (I 'm no sql guru though so I can't say with confidence):

SELECT val, tally
FROM (SELECT val, COUNT( * ) AS tally
      FROM sometable
      GROUP BY val
      ORDER BY tally DESC)
      temp
WHERE tally >1