CODEWITHSUNDEEP
postgresql
Tanay Sharma
Tanay Sharma

Reputation: 1164

Postgres count month on the basis of user_id

I want to count number of month for particular user from subscription table. for example user_id = 1 occur 10 times in subscription table like in January it appears 2 time and in February = 0 and again in march = 1 like that

user_id   type      started_on  ended_on
 2       P          2009-10-21  2010-03-18  
 2       F          2010-03-18  2010-03-20
 2       P          2010-03-20  2012-05-19
 2       F          2012-05-19  till now

Upvotes: 0

Views: 61

Answers (1)

perzsa
perzsa

Reputation: 291

This is pretty basic SQL, I reccomend you read some manual before asking it here. About the Aggregate functions for example. But there you go:

If you want one user:

SELECT 
      count(distinct month)
  FROM 
      subscription 
 WHERE 
      user_id=your_user_id_number

If you want every user's:

SELECT 
      count(distinct month),
      user_id
  FROM 
      subscription 
 GROUP BY 
      user_id

Edit: Ok, so you want the month difference between two date columns, here is how you do it with age():

SELECT user_id, extract(YEAR from age(coalesce(ended_on,current_date),started_on)) * 12 + extract(MONTH FROM age(coalesce(ended_on,current_date),started_on)) 
FROM subscription

Upvotes: 1

Related Questions