Илья Смирнов Илья Смирнов - 3 months ago 11
SQL Question

Postgres count unique result in GROUP BY

I have a table:

id | parent_id | create_time
---|------------|--------------------
1 | 1 | 2016-08-26 00:00:00
2 | 2 | 2016-08-24 00:00:00
3 | 2 | 2016-08-22 00:00:00
4 | 4 | 2016-07-26 00:00:00
5 | 5 | 2016-07-24 00:00:00


I need to count unique 'parent_id' for each month(week,day).

Output something like this:

---------------------|----
2016-08-01 00:00:00 | 2
2016-07-01 00:00:00 | 2


But I could only do so:

SELECT date_trunc('month', create_time),count(parent_id) FROM test GROUP BY
date_trunc('month', create_time),parent_id


Result:

--------------------|---
2016-07-01 00:00:00 | 1
2016-07-01 00:00:00 | 1
2016-08-01 00:00:00 | 1
2016-08-01 00:00:00 | 2


I tried a lot of options, but do not have enough knowledge.

Answer

You need to remove the parent_id from the group by clause. And you probably want to use count(distinct parent_id) as well:

SELECT date_trunc('month', create_time),
       count(distinct parent_id) 
FROM test 
GROUP BY date_trunc('month', create_time)
Comments