Select a count with some conditions Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum count of 20 in the past 24 hours. So that it looks like this:

120 / home
 56 / about
 24 / locations


I was thinking:

select count(*),page from page_views group by page where count(*) &gt; 20;


but this does not work. How to do this?
It's unclear whether you want the total count or the count in the past 24 hours. For the latter:

SELECT count(*), page
FROM   page_views
GROUP  BY 2
WHERE  created_at &gt;= now() - interval '24 hours' -- guessing column
HAVING count(*) &gt; 20
ORDER  BY 1 DESC;


The condition count(*) &gt; 20 has to go into the HAVING clause.

You did not provide a table definition. Guessing you have a created_at to store the event time.

Total counts, but only for pages having a minimum count of 20 in the past 24 hours:

SELECT count(*), page
FROM  (  
    SELECT page
    FROM   page_views
    GROUP  BY 1
    WHERE  created_at &gt;= now() - interval '24 hours'
    HAVING count(*) &gt; 20
    ) x
JOIN   page_views p USING (page)
GROUP  BY 2
ORDER  BY 1 DESC;