PostgreSQL generate_series with WHERE clause

Question

I'm having an issue generating a series of dates and then returning the COUNT of rows matching that each date in the series.

SELECT generate_series(current_date - interval '30 days', current_date, '1 day':: interval) AS i, COUNT(*)
FROM download
WHERE product_uuid = 'someUUID'
AND created_at = i
GROUP BY created_at::date
ORDER BY created_at::date ASC

I want the output to be the number of rows that match the current date in the series.

05-05-2018, 35
05-06-2018, 23
05-07-2018, 0
05-08-2018, 10
...

The schema has the following columns: id, product_uuid, created_at. Any help would be greatly appreciated. I can add more detail if needed.

Gordon Linoff · Accepted Answer · 2018-06-02 11:55:40Z

3

Put the table generating function in the from and use a join:

SELECT g.dte, COUNT(d.product_uuid)
FROM generate_series(current_date - interval '30 days', current_date, '1 day':: interval
                    ) gs(dte) left join
     download d
     on d.product_uuid = 'someUUID' AND
        d.created_at::date = g.dte
GROUP BY g.dte
ORDER BY g.dte;

answered Jun 2, 2018 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL generate_series with WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related