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.

Depending on you flavor of SQL, you can also imply the else statement in your aggregate counts.

For example, here's a simple table Grades:

<!-- language: lang-none --> <pre><code>| <b>Letters</b> | |---------| | A | | A | | B | | C |</code></pre>

We can test out each Aggregate counter syntax like this (Interactive Demo in SQL Fiddle):

<!-- language: lang-sql -->
SELECT
    COUNT(CASE WHEN Letter = 'A' THEN 1 END)           AS [Count - End],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END) AS [Count - Else Null],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)    AS [Count - Else Zero],
    SUM(CASE WHEN Letter = 'A' THEN 1 END)             AS [Sum - End],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END)   AS [Sum - Else Null],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)      AS [Sum - Else Zero]
FROM Grades

And here are the results (unpivoted for readability):

<!-- language: lang-none --> <pre><code>| <b>Description</b> | <b>Counts</b> | |-------------------|--------| | Count - End | 2 | | Count - Else Null | 2 | | Count - Else Zero | 4 | *<b>Note</b>: Will include <i>count</i> of <i>zero</i> values | Sum - End | 2 | | Sum - Else Null | 2 | | Sum - Else Zero | 2 |</code></pre>

Which lines up with the docs for Aggregate Functions in SQL

Docs for COUNT:

COUNT(*) - returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) - evaluates expression for each row in a group, and returns the number of nonnull values.
COUNT(DISTINCT expression) - evaluates expression for each row in a group, and returns the number of unique, nonnull values.

Docs for SUM:

ALL - Applies the aggregate function to all values. ALL is the default.
DISTINCT - Specifies that SUM return the sum of unique values.