using sql count in a case statement

Question

I have a table and i need to present the output in the following fashion.

tb_a:  col1  |  reg_id | rsp_ind  

Count of rows with rsp_ind = 0 as 'New' and 1 as 'Accepted'

The output should be

NEW | Accepted 9   | 10 

I tried using the following query.

select    case when rsp_ind = 0 then count(reg_id)end as 'New',   case when rsp_ind = 1 then count(reg_id)end as 'Accepted' from tb_a 

and i m getting output as

NEW | Accepted NULL| 10 9   | NULL 

Could someone help to me tweak the query to achieve the output. Note : I cannot add a sum surrounding this. Its part of a bigger program and so i cannot add a super-query to this.

Answer 1

SELECT      COUNT(CASE WHEN rsp_ind = 0 then 1 ELSE NULL END) as "New",     COUNT(CASE WHEN rsp_ind = 1 then 1 ELSE NULL END) as "Accepted" from tb_a 

You can see the output for this request HERE