To filter the results of a SQLite tally for values with a certain frequency, use a HAVING instead of a WHERE clause
SELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
HAVING COUNT(*) > 100;
I was using SQLite to tally some values in a table, which I’ve done a bunch of times:
SELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
LIMIT 10
This will print the 10 most frequent tally items, which is useful for exploring the data, but today I wanted to see items above a certain frequency – all values that appear 100 times or more.
I tried to write this with a WHERE clause:
SELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
WHERE COUNT(*) > 100
but I got an error: “Error: misuse of aggregate: count()”.
I found a Stack Overflow answer that mentions the HAVING clause, which does what I want:
SELECT col_name, COUNT(*)
FROM tbl_name
GROUP BY col_name
HAVING COUNT(*) > 100