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?

Why not `COUNT(DISTINCT users.confirmed_at) AS confirmed_class_count`? — Parfait, Sep 06 '19 at 18:57
@Parfait while `confirmed_at` is a timestamp and likely to be unique enough per `user`, this includes null values and I need those to be excluded. Using your code, the count is still too high — Glenn, Sep 06 '19 at 20:06
Odd as `COUNT` always ignores NULL values. Likely it is counting the unique timestamps. — Parfait, Sep 06 '19 at 20:13
Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS When you get an error or behaviour you don't understand: Make it clear that your question is about *that error* & ask re your overall goal later in a new post. PS Please clarify via edits, not comments. — philipxy, Sep 06 '19 at 22:13
This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) — philipxy, Sep 06 '19 at 22:19

FuzzyTree · Accepted Answer · 2019-09-06T20:34:30.273

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

edited Sep 06 '19 at 20:34

answered Sep 06 '19 at 20:07

FuzzyTree

32,014
3
54
85

Thanks for spotting that typo. Also, your query works for counting users, but I need to be counting classes. If 2 people sign up for the class, it gets counted as 2 instead of 1. I'm trying to answer the question of "how many classes have at least one user with a confirmed_at set?" If I change your line to `count(distinct case when users.confirmed_at is not null then subclasses.id end) as confirmed_case_count` then my query works. I couldn't get that working with the `sum` function I had so thank you for your help. If you can tweak your answer to match that change, I'll mark it as the Answer. – Glenn Sep 06 '19 at 20:32

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

1 Answers1