Count PostgreSQL with condition

Question

I have a table for persons and other for visits. I need to count the visits for the month only if it was the first one. So basically get a count of visits for the month but only if your visit for the month, was the first.

Example:

PEOPLE:
   id   
---------
   20   
   30   
   23   

VISITS
  id     |   date
-------------------------
  20     |   09-20-2019
  20     |   10-01-2019
  23     |   10-09-2019
  30     |   10-07-2019

I want to know the coutning only if its the first one, on this example, the counting should equal to 2 because person with ID 20 had a past visit last month.

This is what I have so far on my query

SELECT * FROM visits
LEFT JOIN people ON visits.id = people.id
WHERE date_trunc('month', current_date) <= visits.visit_date AND visits.visit_date < date_trunc('month', current_date) + INTERVAL '1 month'

This is just giving me the visits for the month. How can I filter by only if the person doesnt have past visits.

1 is 1. What difference does it make if the visit that gets counted is the first, the last, or the one in the middle? Sounds like you want count(distinct people.id) — jjanes
– jjanes, Commented Oct 24, 2019 at 23:41

klin · Accepted Answer · 2019-10-24 23:39:41Z

1

You can simplify the condition by just comparing months. Add not exists to check whether the person had a visit before the current month.

select * 
from visits
left join people on visits.id = people.id
where date_trunc('month', current_date) = date_trunc('month', visits.visit_date)
and not exists (
    select from visits
    where id = people.id
    and date_trunc('month', current_date) > date_trunc('month', visits.visit_date)
    )

answered Oct 24, 2019 at 23:39

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

MCM13 Over a year ago

Thank you this helped me a lot!! :)

Ed Bangga · Accepted Answer · 2019-10-25 00:12:09Z

0

from what I understand on your requirement you just need to get the person Ids that had previous visit.

my proposal is to use dense_rank() to based on date_trunc('month', visit_date)

select v.id from visits v
inner join 
  (select dense_rank() over (partition by date_trunc('month', visit_date) order by visit_date) as rn
  , *
  from visits) t1 on t1.id = v.id
group by v.id, t1.rn
having count(v.id) > 1

answered Oct 25, 2019 at 0:12

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

Count PostgreSQL with condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related