5

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?

  • 1
    Look up the HAVING clause. – Craig Ringer Jul 4 '15 at 0:52
  • You missed the 24 hours condition in your where statement. – oNare Jul 4 '15 at 3:00
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.