如何在特定条件后对特定列求和(postgres)

kuarbcqp 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(441)

我有这个密码

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 . 你知道怎么做吗？谢谢您。

sql postgresql metabase

来源：https://stackoverflow.com/questions/63157952/how-to-sum-a-specific-column-after-a-specific-criteria-postgres

1条答案

按热度按时间

zwghvu4y1#

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

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

赞(0）回复(0）举报 2021-07-27

我来回答

如何在特定条件后对特定列求和(postgres)

1条答案

相关问题

热门标签

最新问答