Question 90487 in PostgreSQL Question posted in
The official documentation can be found here.

Counting total of matching rows without filtering them with join table – Postgresql

I have the following structure:

  • Training Type
    • Contains multiple sessions
      • Each session can have multiple slots
        • A slot has a start date. (date column)

How to compute for each Training Type the total of sessions whose all the slots are later than today ?

What’s important here is that I can’t use a WHERE statement otherwise it will remove the row training 3.

I tried that but the result is wrong, it counts the total of joined rows on sessions.

I also tried the case method but I’m not sure if the method is not right, or my min(date) function is not behaving as expected

I don’t really have an advanced level in SQL so I don’t know what exact terms I should look for

Some data:

  1. TrainingType
id name required_age category
2 BNSSA 18 Sports Nautiques
3 PSE1 16 Secourisme
4 PSE2 16 Secourisme
5 PSC1 10 Secourisme
  1. Training Sessions
id duration capacity default_location_id type_id
1ed73d5c-4eb8-65c8-ae00-fbb7d44c5577 12 14 ChIJ2WHniqnTzRIR__WsniGZpIA 2
1ed7ef8e-2f71-631a-a42b-eb7079923973 9 10 ChIJqxJeRH8V5kcRTsGGwB0ujYA 3
1ed7efdd-b3a5-6182-9114-e3ba8bb75fdd 10 10 ChIJuTbG23Vw5kcRHE2Nm7oV4rQ 2
1ed7efdf-6d7d-6518-a6fd-9ba374e38b99 3 10 ChIJKwXG2IBJrhIRUFAwL8YPQi4 2
  1. Training sessions
id start duration location_id training_session_id
1ed73d5c-4eb9-6284-b428-fbb7d44c5577 2022-12-19 14:00:00 3 1ed73d5c-4eb8-65c8-ae00-fbb7d44c5577
1ed73d5c-4eb9-6590-a1d4-fbb7d44c5577 2022-12-20 14:00:00 3 1ed73d5c-4eb8-65c8-ae00-fbb7d44c5577
1ed7ef8e-2f74-6830-950c-eb7079923973 2022-12-21 14:00:00 7 1ed7ef8e-2f71-631a-a42b-eb7079923973
1ed7ef8e-2f74-6ba0-b4c0-eb7079923973 2022-12-22 13:00:00 3 1ed7ef8e-2f71-631a-a42b-eb7079923973
1ed7efdd-b3a9-6034-bdbc-e3ba8bb75fdd 2022-12-12 13:00:00 3 1ed7efdd-b3a5-6182-9114-e3ba8bb75fdd
1ed7efdd-b3a9-63e0-bb04-e3ba8bb75fdd 2022-12-20 12:00:00 3 1ed7efdd-b3a5-6182-9114-e3ba8bb75fdd
1ed7efdf-6d7e-6b0c-ad5c-9ba374e38b99 2022-12-15 14:00:00 3 1ed7efdf-6d7d-6518-a6fd-9ba374e38b99

Expected output:

id name required_age category sessionsCount
2 BNSSA 18 Sports Nautiques 1
3 PSE1 16 Secourisme 1
4 PSE2 16 Secourisme 0
5 PSC1 10 Secourisme 0
Tags:

2

Answers


  1. 0

    Using a subquery:

    select t.id, t.name, t.required_age, t.category, (select count(*) 
    from TrainingSessions t1 where t.id = t1.type_id and not exists 
     (select 1 from Training_sessions t2 where t2.training_session_id = t1.id and t2.start <= now()))
from TrainingType t

    See fiddle.

    Login or Signup to reply.
  2. 0

    You could use aggregation and left join as the following:

    select TT.id, TT.name, TT.required_age, TT.category, count(D.type_id)
from TrainingType TT left join
(
  select T1.training_session_id, T2.type_id
  from TrainingSessionsSlots T1 join TrainingSessions T2
  on T1.training_session_id = T2.id
  group by T1.training_session_id, T2.type_id
  having min(start) > now()
) D
on TT.id = D.type_id
group by TT.id, TT.name, TT.required_age, TT.category
order by TT.id

    having min(start) > now() ensures that all of the slot dates are greater than now (within training_session_id, type_id group).

    count(D.type_id) counts only not null values, so when there is no match from the left join the D.type_id value will be null and the count(D.type_id) will be 0.

    See demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search