Select multiple columns of count for different column value

Question

I have tables like this:

ID          Status
----------- -----------------
a            3
b            3
c            1
d            2
e            1

How can i query it such that the count for each status is in 1 column:

Status=1  Status=2   Status=3
--------  --------  --------
2          1          2

is it necessary to Select from multiple Select of Count here? NOTE: the Status is not fixed and may contain values besides 1, 2 and 3

Is Status column dynamic? Is that fix that it will contain 1,2,3 only? — Rushabh Master
– Rushabh Master, Commented Dec 7, 2016 at 11:21
Then see my ans in Answers.. that can help you with different values — Rushabh Master
– Rushabh Master, Commented Dec 7, 2016 at 12:02

Pரதீப் · Accepted Answer · 2016-12-07 11:11:55Z

4

Use Conditional Aggregate

select count(case when Status = 1 then 1 end) as [Status=1],
       count(case when Status = 2 then 1 end) as [Status=2],
       count(case when Status = 3 then 1 end) as [Status=3]
From yourtable

same count can be achieved through SUM aggregate

Sum(case when Status = 1 then 1 else 0 end) as [Status=1]

answered Dec 7, 2016 at 11:11

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rushabh Master · Accepted Answer · 2016-12-07 11:46:52Z

3

Try this, This is dynamic

select 'Status = '+cast(Status as varchar(200)) Status,count(Status) Count from yourtable group by Status

answered Dec 7, 2016 at 11:46

Rushabh Master

5104 silver badges13 bronze badges

2 Comments

Pரதீப் Over a year ago

Upvoted... But changes the result format.. probably OP can follow this format which avoid's hard coding

Rushabh Master Over a year ago

Thank you for your vote @prdp but converting it to pivot would also get ugly.so i kept it this way.

Collectives™ on Stack Overflow

Select multiple columns of count for different column value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related