CODEWITHSUNDEEP
databasepostgresql
roy naufal
roy naufal

Reputation: 399

finding number of day occurrences for each id

I am trying to get the number of days a "DID" is present in. I have tried group by & partition table, but wasn't able to figure it out

To elaborate, this is the data:

DID  timestamp 
a   2019-02-02 12:52:00
a   2019-02-03 13:23:00
a   2019-02-03 11:45:00
a   2019-02-04 15:21:00
b   2019-02-02 12:18:00
b   2019-02-02 13:54:00
b   2019-02-02 18:27:00
b   2019-02-05 19:31:00

a occurs once on the 2nd, twice on the 3rd, and once on the 4th, so in total 'a' is present 4 times on 3 dates

b occurs 3 times on the 2nd, once on the 5th, so 4 times on 2 dates

what I am trying to get is such an output:

DID presence_on_number_of_days
a   3
b   2

Any help would be greatly appreciated!

Upvotes: 0

Views: 24

Answers (2)

forpas
forpas

Reputation: 164154

You need to count the distinct dates:

select DID, count(distinct timestamp::date) presence_on_number_of_days
from tablename
group by DID

Upvotes: 1

sticky bit
sticky bit

Reputation: 37482

You can use date_trunc() to extract the timestamp only accurate to the day and count() with DISTINCT. That way a day is only counted once (per did).

SELECT did,
       count(DISTINCT date_trunc('day', timestamp)) presence_on_number_of_days
       FROM elbat
       GROUP BY did;

Upvotes: 1

Related Questions