时间戳

首页 > 解决方案 > 如何在特定条件后对特定列求和（Postgres）

sql - 如何在特定条件后对特定列求和（Postgres）

问题描述

我有这个代码

SELECT s.name AS School,
        count(distinct u.id) filter (where ut.app_name = 'A' or ut.app_name = 'G') AS A,
        count(distinct u.id) filter (where ut.app_name = 'B' or ut.app_name = 'J') AS B,
        count(distinct u.id) filter (where ut.app_name = 'C') AS C,
        count(distinct u.id) filter (where ut.app_name = 'D') AS D
FROM "public"."user_tokens" ut JOIN
     "public"."users" u
      ON ut.user_id = u.id JOIN
      "public"."user_roles" ur
      ON ut.user_id = ur.user_id JOIN
      "public"."roles" r
      ON ur.role_id = r.id JOIN
      "public"."schools" s
      ON ur.school_id = s.id
GROUP BY s.name
having count(distinct u.id) filter (where ut.app_name = 'A' or ut.app_name = 'G') > 0
    and count(distinct u.id) filter (where ut.app_name = 'B' or ut.app_name = 'J') > 0
    and count(distinct u.id) filter (where ut.app_name = 'C') > 0

上面代码的结果是

School      A       B       C       D
------------------------------------------
P           5       3       2       5
S           1       4       4       9
T           2       3       5       2
U           2       1       3       1

我想对 A 列求和，所以预期的结果是10. 任何启示如何做到这一点？谢谢你。

标签： sqlpostgresqlmetabase

解决方案

正如您在评论中所写，您已经找到了解决方案，这只是为了展示如何使用别名而不是在 HAVING 中重复计算：

select sum(A)
from
 (
    SELECT s.name AS School,
            count(distinct u.id) filter (where ut.app_name = 'A' or ut.app_name = 'G') AS A,
            count(distinct u.id) filter (where ut.app_name = 'B' or ut.app_name = 'J') AS B,
            count(distinct u.id) filter (where ut.app_name = 'C') AS C,
            count(distinct u.id) filter (where ut.app_name = 'D') AS D -- can be removed, not used
    FROM "public"."user_tokens" ut JOIN
         "public"."users" u
          ON ut.user_id = u.id JOIN
          "public"."user_roles" ur
          ON ut.user_id = ur.user_id JOIN
          "public"."roles" r
          ON ur.role_id = r.id JOIN
          "public"."schools" s
          ON ur.school_id = s.id
    GROUP BY s.name
 ) as dt
where A> 0
  and B > 0
  and C > 0

推荐阅读