Using case, count, and group by in 1 query

I have 3 tables that I need to join, count/sum data on, and group the data. Below are very simplified versions of my tables.

    People p
ID 
1
2
3
    Accounts a
ID | Balance | PeopleFK
1  | 50      | 1
2  | 25      | 2
3  | 15      | 3

    Cards c
ID | Status | PeopleFK
1  | Active | 1
2  | Inact  | 1
3  | Active | 2
4  | Other  | 2

What I have so far:

select sum(a.balance),count(c.*),
case when c.status='Active' then 'Active'
case when c.status IN ('Inactive','Other') then 'Inact'
case when c.status is null then 'No Card' end as 'CardStatus'
from people p
join accounts a on p.id=a.PeopleFK
left join cards c on p.id=c.PeopleFK
group by c.status

Required result would be something like:

Balance  |  CardStatus  |  CardCount
50       |  Active      |  2
25       | Inactive     |  1
0        | NoCard       |  1

But my result groups by all of the Card statuses. I understand why this happens but I'm not sure how I can get around this without writing separate queries for each case.

So instead of Inactive and Other grouping together, they group apart.

I'm still toying with this but any suggestions?

When using Group By, the SELECT statement may only contain fields in the Group By statement or aggregate functions (like SUM or COUNT).

You can use a Common Table Expression (CTE) to consolidate the logic of building the CardStatus into a select statement, and then funnel that into a new query which leverages it in both the group by and select statements

<!-- language: lang-sql -->

;WITH BalanceCards AS (
    SELECT a.balance,
           (CASE WHEN c.status='Active'                THEN 'Active'
                 WHEN c.status IN ('Inactive','Other') THEN 'Inact'
                 WHEN c.status IS NULL                 THEN 'No Card' 
            END) AS CardStatus
    FROM people p
    JOIN accounts a ON p.id = a.PeopleFK
    LEFT JOIN cards c ON p.id = c.PeopleFK
)
SELECT bs.CardStatus, 
       SUM(bs.balance) As Total, 
       COUNT(*) As [Count]
FROM BalanceCards bs
GROUP BY bs.CardStatus