SQL Query to get multiple resultant on single column

Question

I have a table that looks something like this:

id name status
2  a     1
2  a     2
2  a     3
2  a     2
2  a     1
3  b     2
3  b     1
3  b     2
3  b     1

and the resultant i want is:

id  name   total count    count(status3)   count(status2)   count(status1)
2   a        5                  1              2              2
3   b        4                  0              2              2

please help me get this result somehow, i can just get id, name or one of them at a time, don't know how to put a clause to get this table at once.

I've gone ahead and removed the tag spam here; please only tag the RDBMS you are *really* using. Tagging multiple conflicting technologies makes your question unclear and thus *harder* to answer. It is also unlikely to get your question "more views" and likely to have the opposite effect, where only users who know *all* the RDBMS you have (mis)tagged will entertain the question; that is often a very small portion of the user base. Tag spam is far more likely to result in your question receiving downvotes and/or being closed as unclear. Please [edit] your question to retag the correct RDBMS. — Thom A, Aug 27 '22 at 17:42
Tagging RDBMS doesn't tell us what RDBMS you are using... Telling us you are using an RDBMS when asked what RDBMS are you using, is telling us "An Operating system" when asked "What Operating System are you using?" — Thom A, Aug 27 '22 at 17:53

DannySlor · Accepted Answer · 2022-08-27T18:39:27.490

0

Here's a simple solution using group by and case when.

select id
      ,count(*) as 'total count'
      ,count(case status when 3 then 1 end) as 'count(status1)'
      ,count(case status when 2 then 1 end) as 'count(status3)'
      ,count(case status when 1 then 1 end) as 'count(status2)'
from t 
group by id

id	total count	count(status3)	count(status2)	count(status1)
2	5	1	2	2
3	4	0	2	2

Fiddle

edited Aug 27 '22 at 18:39

answered Aug 27 '22 at 17:47

DannySlor

4,574
1
3
11

Thanks a lot man, it works great. have a small theoretical doubt, what does 'then 1' signifies? – Milind Sahu Aug 27 '22 at 18:05
It can be anything. It only counts what's not null so you can do "then 2" then 'a'" etc. – DannySlor Aug 27 '22 at 18:10

DannySlor · Answer 2 · 2022-08-27T18:58:24.023

0

Here's a way to solve it using pivot.

select *
from  (select status,id, count(*) over (partition by id) as "total count" from t) tmp
       pivot (count(status) for status in ([1],[2],[3])) pvt

d	total count	1	2	3
3	4	2	2	0
2	5	2	2	1

Fiddle

edited Aug 27 '22 at 18:58

answered Aug 27 '22 at 18:41

DannySlor

4,574
1
3
11

SQL Query to get multiple resultant on single column

2 Answers2