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.

u cant add a sum but can u change the query? or make this a subquery? — Jafar Kofahi, Jul 31 '13 at 16:01
i can add a subquery instead of case statement. But not a super query above this... Adding a subquery obviously comes with performance hit... — Raghav, Jul 31 '13 at 16:07
I see.. then maybe you can add this subquery to your join clause instead of making an inline subquery..is that possible in ur code? — Jafar Kofahi, Jul 31 '13 at 16:09
Please show your whole query, in particular your `GROUP BY` clause! Also, what DBMS and version--Oracle, MySQL, SQL Server 2008, what? — ErikE, Jul 31 '13 at 16:18

BMN · Accepted Answer · 2013-07-31T16:16:45.300

172

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

edited Jul 31 '13 at 16:16

answered Jul 31 '13 at 16:01

BMN

8,253
14
48
80

this wouldnt go with what the OP asks for – Jafar Kofahi Jul 31 '13 at 16:02
2

@JafarKofahi feel free to explain yourself ! It seems to me that it's exactly what he's loooking for... – BMN Jul 31 '13 at 16:04
in the OP he said "Note : I cannot add a sum surrounding this. Its " – Jafar Kofahi Jul 31 '13 at 16:05
Tell me where do you see a sum ? – BMN Jul 31 '13 at 16:06
2

I think he meant that he cant use any aggregate functions since its a part of a bigger query coz he cant add a `group by` – Jafar Kofahi Jul 31 '13 at 16:06
I can add a group by to the select. – Raghav Jul 31 '13 at 16:08
The output is same as the current one, but instead of null, it gives 0s... I need to merge the rows to a single one... – Raghav Jul 31 '13 at 16:09
@Raghav take a look at my SQLFiddle. – BMN Jul 31 '13 at 16:16

score 21 · Answer 2 · answered Aug 30 '18 at 20:54

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:

| Letters |
|---------|
| A       |
| A       |
| B       |
| C       |

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

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):

|    Description    | Counts |
|-------------------|--------|
| Count - End       |    2   |
| Count - Else Null |    2   |
| Count - Else Zero |    4   | *Note: Will include count of zero values
| Sum - End         |    2   |
| Sum - Else Null   |    2   |
| Sum - Else Zero   |    2   |

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.

Charles Bretana · Answer 3 · 2013-07-31T17:48:35.253

15

Close... try:

select 
   Sum(case when rsp_ind = 0 then 1 Else 0 End) as 'New',
   Sum(case when rsp_ind = 1 then 1 else 0 end) as 'Accepted'
from tb_a

edited Jul 31 '13 at 17:48

answered Jul 31 '13 at 16:01

Charles Bretana

143,358
22
150
216

this wouldnt go with what the OP asks for – Jafar Kofahi Jul 31 '13 at 16:02
The OP says that this gives him two rows instead of one. – ErikE Jul 31 '13 at 16:20
typo only - left off one closing parentheses. Surprised it even worked. – Charles Bretana Jul 31 '13 at 17:48
1

I'm sure he figured out the parenthesis and I'm sure that its lack is not what made two rows appear instead of one. – ErikE Aug 01 '13 at 05:48
"It's not what you don't know that'll hurt you, it's what you do know that ain't so." – Charles Bretana Jan 17 '17 at 14:06

ErikE · Answer 4 · 2013-08-01T05:52:06.200

2

The reason you're getting two rows instead of one is that you are grouping by rsp_ind in the outer query (which you did not, to my disappointment, share with us). There is nothing you can do to force one row instead of two without dealing with that GROUP BY item.

edited Aug 01 '13 at 05:52

answered Jul 31 '13 at 16:21

ErikE

48,881
23
151
196

score 2 · Answer 5 · edited Apr 02 '21 at 10:00

2

If you want to group the results based on a column and take the count based on the same, you can run the query as :

$sql = "SELECT COLUMNNAME,

COUNT(CASE WHEN COLUMNNAME IN ('YOURCONDITION') then 1 ELSE NULL END) as 'New',

COUNT(CASE WHEN COLUMNNAME IN ('YOURCONDITION') then 1 ELSE NULL END) as 'ACCPTED'

FROM TABLENAME

GROUP BY COLUMNANME";

edited Apr 02 '21 at 10:00

Atika

1,025
2
6
17

answered Aug 20 '19 at 10:41

Varun P V

1,092
1
12
30

score 2 · Answer 6 · answered May 18 '20 at 22:43

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

score 0 · Answer 7 · answered Jul 31 '13 at 16:02

0

select sum(rsp_ind = 0) as `New`,
       sum(rsp_ind = 1) as `Accepted` 
from tb_a

answered Jul 31 '13 at 16:02

juergen d

201,996
37
293
362

3

An important detail given the OP didn't say what DBMS this is for. – ErikE Aug 01 '13 at 05:50

score 0 · Answer 8 · answered May 24 '19 at 11:40

ok. I solved it

SELECT `smart_projects`.project_id, `smart_projects`.business_id, `smart_projects`.title,
 `page_pages`.`funnel_id` as `funnel_id`, count(distinct(page_pages.page_id) )as page_count, count(distinct (CASE WHEN page_pages.funnel_id != 0 then  page_pages.funnel_id ELSE NULL END ) ) as funnel_count
FROM `smart_projects`
LEFT JOIN `page_pages` ON `smart_projects`.`project_id` = `page_pages`.`project_id`
WHERE  smart_projects.status !=  0 
AND `smart_projects`.`business_id` = 'cd9412774edb11e9'
GROUP BY `smart_projects`.`project_id`
ORDER BY `title` DESC

score 0 · Answer 9 · answered Apr 25 '23 at 15:14

Added this solution, as I came here while searching for how to classify and count data in one statement. Using a double select you can get a query that has the count and the 'classification' in the result. Hope someone finds this useful.

SELECT A, COUNT(A) AS Count FROM (
        SELECT 
            CASE
                WHEN [Col1] = 'Option1' OR [Col1] = 'Option2'
                THEN 'Classification 1'
                 
                WHEN [Col1] = 'Option3' OR 'Option4' 
                THEN 'Classification 2'
    
                WHEN [Col1] = 'Option4' 
                THEN 'Classification 3'
            END As A
          FROM [Table1] 
          ) AS G
    GROUP BY A

score -1 · Answer 10 · answered Jul 08 '23 at 05:27

-1

Simple:

SELECT
    COUNT(CASE WHEN user_type = 'Admin' THEN 1 ELSE NULL END) AS mid_dead_count
FROM
    users
WHERE
    DATE = '2022-01-02';

answered Jul 08 '23 at 05:27

Deepanshu Mehta

1,119
12
9

using sql count in a case statement

10 Answers10

Linked