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

我有一张有保险索赔表。它具有ID，患者具有的条件，ClaimID，EmergencyDepartmentFlag(1-ED; 0-NonED。我需要获取结果，该结果可以让我了解每种情况下的索赔数量以及每种情况下的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

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

最佳答案

只是使用这个

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

结果

关于sql - SQL在不同条件下连接同一张表并获取计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48010373/