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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select sum rsp ind   0  as  New          sum rsp ind   1  as  Accepted   from tb a

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] using sql count in a case statement

Examples related to sql