Postgres count unique result in GROUP BY

Question

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.

user330315 · Accepted Answer · 2016-08-30 11:26:56Z

2

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)

answered Aug 30, 2016 at 11:26

user330315

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

Comments

Christian · Accepted Answer · 2016-08-30 11:26:31Z

0

Try to use

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

answered Aug 30, 2016 at 11:26

Christian

8276 silver badges15 bronze badges

Collectives™ on Stack Overflow

Postgres count unique result in GROUP BY

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related