Search code examples
mysqlfrequency

Mysql count frequency - with threshold

Posts like Mysql count frequency were useful in getting a frequency list of a field in a MySQL table.

What if I'd like to set a threshold, so that only some frequencies that fulfil a criteria are "selected" or shown. eg.

SELECT age, COUNT(*) AS freq FROM ages GROUP BY age WHERE COUNT(*) > 20

The above command gets an error though. What is the correct command?

Solution

  • You have to use HAVING but you have to do the filter on the new field "freq":

    SELECT age, COUNT(*) AS freq
FROM ages
GROUP BY age
HAVING freq > 20;