sql 将多行总计选择为每个 account_ID 一行

Question

我的目标是将所有 3 行排成一行。我试图只取回 1 行。

select br_data.acct_id ,
bs_accts.acct_num, 
case br_data.recmethod
when 1 then count(br_data.recmethod) 
else 0 end as "Open", 
case br_data.recmethod
when 2 then count(br_data.recmethod) 
else 0 end as "Closed", 
case br_data.recmethod
when 0 then count(br_data.recmethod)
else 0 as "Suspended"
from br_data , bs_accts
where  br_data.acct_id = bs_accts.acct_id
and br_data.acct_id = '427'
group by br_data.acct_id , bs_accts.acct_num  , br_data.recmethod
order by br_data.acct_id

当前结果:

acct_id   acct_num      open    closed     suspended
427   0060-1537100-OLD    0         0        376818
427   0060-1537100-OLD    2279474   0          0
427   0060-1537100-OLD    0        82675       0

期望的结果:

acct_id   acct_num         open    closed     suspended
427   0060-1537100-OLD    2279474   82675      376818

Answer 1

只需从 GROUP BY 子句中删除 br_data.recmethod 即可:

SELECT
    bd.acct_id,
    ba.acct_num,
    SUM(CASE WHEN bd.recmethod = 1 THEN 1 ELSE 0 END) AS [Open],
    SUM(CASE WHEN bd.recmethod = 2 THEN 1 ELSE 0 END) AS [Closed],
    SUM(CASE WHEN bd.recmethod = 0 THEN 1 ELSE 0 END) AS [Suspended]
FROM br_data bd
INNER JOIN bs_accts ba
    ON ba.acct_id = bs.acct_id
WHERE
    bd.acct_id = '427' -- You may want to remove the quotes if this column is of INT type
GROUP BY
    bd.acct_id, bs.acct_num
ORDER BY bd.acct_id

---

注释

1. Avoid using old-style JOIN syntax.
2. 如果 ba.account_id 的数据类型为 INT，您可能需要删除 427 上的引号。
3. 使用别名并使用 [] 括起您的列名称。