时间戳

首页 > 解决方案 > 如何使用聚合函数进行 GROUP BY CASE

sql - 如何使用聚合函数进行 GROUP BY CASE

问题描述

我试图根据一行显示的 a.stat 值来获取 a.clmNo 的计数。我目前正在返回 3 行，因为我在 3 个不同的 a.stat 值上有 5 个 a.clmNo 计数，这是有道理的，因为我按 s.stat 分组。如何更改我的查询，以便不必按 s.stat 分组，而是在一行上返回结果？

当前结果：

+-------------+--------------+-----------------+-----------+---------------+
| pend_claims | assnd_claims | qa_ready_claims | qa_claims | closed_claims |
+-------------+--------------+-----------------+-----------+---------------+
|       0     |       3      |        0        |     0     |       0       |
+-------------+--------------+-----------------+-----------+---------------+
|       0     |       0      |        0        |     1     |       0       |
+-------------+--------------+-----------------+-----------+---------------+
|       1     |       0      |        0        |     0     |       0       |
+-------------+--------------+-----------------+-----------+---------------+

期望的结果：

+------------+--------------+-----------------+-----------+--------------+
|pend_claims | assnd_claims | qa_ready_claims | qa_claims | closed_claims|
+------------+--------------+-----------------+-----------+--------------+
|       1    |       3      |        0        |      1    |      0       |
+------------+--------------+-----------------+-----------+--------------+

当前查询：

SELECT  ISNULL(case when s.stat = 'Pending Assignment' then count(a.clmNo) end,0) as pend_claims,
    ISNULL(case when s.stat = 'Assigned' then count(a.clmNo) end,0) as assnd_claims,        
    ISNULL(case when s.stat = 'QA Ready' then count(a.clmNo) end,0) as qa_ready_claims,
    ISNULL(case when s.stat = 'In QA' then count(a.clmNo) end,0) as qa_claims,
    ISNULL(case when s.stat = 'Closed' then count(a.clmNo) end,0) as closed_claims
FROM assnmts a
inner join assnmtStats astats
    on a.assnmtIdPk = astats.assnmtIdFk
inner join stats s
    on astats.aStatId = s.statIdPk
inner join repAssnmts ra
    on a.assnmtIdPk = ra.assnmtIdFk
inner join aspnetusers anu
    on ra.repId = anu.Id
inner join clients c
    on a.clientIdFk = c.clientIdPk
inner join carrs
    on a.carrierId = carrs.carrIdPk
inner join (SELECT a2.assnmtIdPk, MAX(astats2.asCrtdDt) as MaxDate 
            FROM assnmts a2 
                INNER JOIN assnmtStats astats2
                    on a2.assnmtIdPk = astats2.assnmtIdFk
            GROUP BY a2.assnmtIdPk
            ) mdt
    on a.assnmtIdPk = mdt.assnmtIdPk
    and astats.asCrtdDt = mdt.MaxDate
inner join (select a3.assnmtIdPk, MAX(ra2.raCrtdDt) as MaxRepDate
            from assnmts a3
            inner join repAssnmts ra2
                on a3.assnmtIdPk = ra2.assnmtIdFk
            group by a3.assnmtIdPk
            ) mrepdt
on a.assnmtIdPk = mrepdt.assnmtIdPk
and ra.raCrtdDt = mrepdt.MaxRepDate
group by s.stat

标签： sqlsql-servertsql

解决方案

您需要条件聚合。删除GROUP BY并改写SELECT：

SELECT SUM(case when s.stat = 'Pending Assignment' then 1 else 0 end) as pend_claims,
       SUM(case when s.stat = 'Assigned' then 1 else 0 end) as assnd_claims,        
       SUM(case when s.stat = 'QA Ready' then 1 else 0 end) as qa_ready_claims,
       SUM(case when s.stat = 'In QA' then 1 else 0 end) as qa_claims,
       SUM(case when s.stat = 'Closed' then 1 else 0 end) as closed_claims

推荐阅读