时间戳

首页 > 解决方案 > 如何获得命名内部联接的不同计数？

sql - 如何获得命名内部联接的不同计数？

问题描述

我已经为信息汇总表构建了一个查询，它几乎就在那里，但有一个小错误。如果一个班级有多个用户，该confirmed_class_count变量返回得太高，导致我相信这个数字并不明显

这是我当前的代码：

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

我想用类似的sum东西替换，(select distinct count(*) from subcases where users.confirmed_at is not null) as confirmed_case_count但我得到了relation "subclasses" does not exist.

我如何在这里得到我想要的东西？

标签： sqlpostgresqlcountinner-join

解决方案

您可以将 count distinct 与条件聚合一起使用。代替

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

和

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

推荐阅读