How can I get a distinct count of a named inner join?

Question

I've built a query for a summary table of information, and it's almost there, with one small bug. The confirmed_class_count variable comes back too high if there's multiple users on a class, leading me to believe that the number isn't distinct

Here's my current code:

SELECT "staffs".*,
  count(distinct subclasses) as class_count,
  sum(case when users.confirmed_at is not null then 1 else 0 end) confirmed_class_count
FROM
  staffs
INNER JOIN classes as subclasses on staffs.staff_id = ANY(subclasses.staff)
INNER JOIN "classes_users" ON "classes_users"."class_id" = "subclasses"."id"
INNER JOIN "users" ON "users"."id" = "classes_users"."user_id"
INNER JOIN class_types ON class_types.code = subclasses.class_type_code
WHERE 
  (subclasses.closed_date is NULL OR subclasses.closed_date > '2019-09-06')
GROUP BY
  staffs.id ORDER BY "staffs"."full_name" ASC

I want to replace the sum with something like (select distinct count(*) from subcases where users.confirmed_at is not null) as confirmed_case_count but I get relation "subclasses" does not exist.

How do I get what I'm intending here?

Answer 1

You can use count distinct with conditional aggregation. Replace

sum(class when users.confirmed_at is not null then 1 else 0 end) confirmed_class_count
    ^ looks like a typo, this should be case not class

with

count(distinct case when users.confirmed_at is not null then classes_users.class_id end) confirmed_class_count