CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Cheslin
Cheslin

Reputation: 27

sql select multiple rows totals into one row per account_ID

My goal is to get all 3 lines in one row. I am trying to only get back 1 lines.

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

Current results:

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

Desired Result:

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

Upvotes: 2

Views: 69

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Just remove br_data.recmethod from your GROUP BY clause:

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

NOTES

  1. Avoid using old-style JOIN syntax.
  2. You may want to remove the quotation marks on 427 if ba.account_id is of INT data type.
  3. Use alias and use [] to enclose your column names.

Upvotes: 5

Related Questions