SQL 在不同条件下连接同一个表并获取计数

Question

我有一张包含保险 claim 的表格。它具有 ID、患者的病情、ClaimID、EmergencyDepartmentFlag(1 - ED; 0 - NonED。我需要获取结果，该结果给出每种情况的 claim 计数和每种情况的 ED 就诊计数

+----------+-----------+----------+--------+
| MemberID | Condition | ClaimID  | EDFlag |
+----------+-----------+----------+--------+
| A123     | COPD      | 34124434 |      1 |
| A526     | COPD      | 34580304 |      0 |
| A693     | COPD      | 23723642 |      1 |
| A645     | DM        | 46534633 |      1 |
+----------+-----------+----------+--------+

预期输出

+-----------+-------------+----------+
| Condition | TotalClaims | EDClaims |
+-----------+-------------+----------+
| COPD      |           3 |        2 |
| DM        |           1 |        1 |
+-----------+-------------+----------+

查询

SELECT condition, 
       Count(a1.claimid) AS TotalClaims, 
       Count(a2.claimid) AS EDClaims 
FROM   (SELECT memberid, 
               condition, 
               claimid 
        FROM   mytable) a1 
       INNER JOIN (SELECT memberid, 
                          condition, 
                          claimid 
                   FROM   mytable 
                   WHERE  edflag = 1) a2 
               ON a1.memberid = A2.memberid 
GROUP  BY condition 

此查询未返回正确的结果。

Answer 1

就用这个

SELECT
    Condition,
    TotalClaims = COUNT(1),
    EDClaims = SUM(CAST(EDFlag AS INT))
    FROM YourTable
    GROUP BY Condition

演示:

DECLARE @T TABLE
(
    Condition VARCHAR(50),
    ClaimID BIGINT,
    EDFlag BIT
)

INSERT INTO @T
VALUES('COPD',34124434,1),
('COPD',34580304,0),
('COPD',23723642,1),
('DM',46534633,1)

SELECT
    Condition,
    TotalClaims = COUNT(1),
    EDClaims = SUM(CAST(EDFlag AS INT))
    FROM @T
    GROUP BY Condition

结果