PostgreSQL – How to Select a Count with Conditions

aggregatepostgresqlpostgresql-9.4

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(*) > 20;

but this does not work. How to do this?

Best Answer

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 >= now() - interval '24 hours' -- guessing column
HAVING count(*) > 20
ORDER  BY 1 DESC;

The condition count(*) > 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 >= now() - interval '24 hours'
    HAVING count(*) > 20
    ) x
JOIN   page_views p USING (page)
GROUP  BY 2
ORDER  BY 1 DESC;
Related Question