CODEWITHSUNDEEP
sqlsql-serverselectcountsum
hello123
hello123

Reputation: 951

How to combine multiple count statements in SQL

I have a table with a list of ID's and each ID has a Status of either HW, HB, PIP or TBC.

I want to count the number of people with each of these statuses, so I would have a table looking like

HW HB PIP TBC

45 27 9 49

At the minute I have

    (SELECT COUNT(*) AS 'StatusHW' From HomeworkerStatus WHERE Status='Homeworker')
    UNION       
    (SELECT COUNT(*) AS 'StatusHB' From HomeworkerStatus WHERE Status='Home Based')
    UNION
    (SELECT COUNT(*) AS 'StatusPIP' From HomeworkerStatus WHERE Status='PIP') 
    UNION
    (SELECT COUNT(*) AS 'StatusTBC' From HomeworkerStatus WHERE Status='TBC')

This returns

StatusHW

1 45

2 27

3 9

4 49

I have also tried

    SELECT Status,
    sum(case when Status = 'Homeworker' then 1 else 0 end) StatusHW,
    sum(case when Status = 'Home Based' then 1 else 0 end) StatusHB,
    sum(case when Status = 'PIP' then 1 else 0 end) StatusPIP,
    sum(case when Status = 'TBC' then 1 else 0 end) StatusTBC
    FROM HomeworkerStatus
    GROUP BY Status

This retunrs a 4x4 table with rows 1,2,3,4 and columns Status,StatusHW,...,StatusTBC. This is closer to what I am after, but still not right.

Any help would be appreciated.

Thanks

Upvotes: 1

Views: 1347

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

Just remove group by as have already dealt with those conditions while aggregating as:

 SELECT 
    -- Status,
    sum(case when Status = 'Homeworker' then 1 else 0 end) StatusHW,
    sum(case when Status = 'Home Based' then 1 else 0 end) StatusHB,
    sum(case when Status = 'PIP' then 1 else 0 end) StatusPIP,
    sum(case when Status = 'TBC' then 1 else 0 end) StatusTBC
    FROM HomeworkerStatus
    -- GROUP BY Status

Upvotes: 6

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Don't GROUP BY Status, if you want only one line:

SELECT
sum(case when Status = 'Homeworker' then 1 else 0 end) StatusHW,
sum(case when Status = 'Home Based' then 1 else 0 end) StatusHB,
sum(case when Status = 'PIP' then 1 else 0 end) StatusPIP,
sum(case when Status = 'TBC' then 1 else 0 end) StatusTBC
FROM HomeworkerStatus;

Upvotes: 5

Related Questions