none
CASE Statement with Aggregate Function

    Question

  • Hello! 

    I have a table whose information are known but queries written against it are not producing the right result set.

    I will demonstrate this with a temp table populated with data.

    CREATE TABLE #tem
    (ID INT NOT Null IDENTITY(1,1) PRIMARY KEY
    , Scope NVARCHAR(30) Not Null
    , Pro NVARCHAR(30) Null
    , Val BIT DEFAULT 0
    )
    
    
    INSERT INTO #tem (Scope,Pro,Val)
    SELECT 'Johnson','Pad','0'
    UNION
    SELECT 'Mark','Shoes','0'
    UNION
    SELECT 'Raff','Wood',''
    UNION 
    SELECT 'Carl','Yam','1'
    UNION 
    SELECT 'Carl','Ball','1'
    UNION 
    SELECT 'Mark','Ball','1'
    UNION
    SELECT 'Johnson','Bag','1'
    UNION 
    SELECT 'Carl','Racket','1'
    UNION
    SELECT 'Carl','PIN','1'
    UNION
    SELECT 'Carl','Ball','0'
    UNION
    SELECT 'Mark','Tie','0'
    UNION
    SELECT 'Mark','Pin','0'

    Run the script below and select from it to understand the data.

    I want to be able to separate the Val column into "One" and "Zero" columns and sum it up and group by the Scope column. So I wrote the following queries but no desired results:

    --Query 1. Good result when no Group By Clause is applied
    SELECT Scope
         , CASE Val WHEN '1' THEN '1' ELSE '' END AS One
         , CASE Val WHEN '0' THEN '0' ELSE '' END AS Zero
    FROM #tem 
    
    
    --Query 2. Not the desired result as Group By Clause is in use
    
    SELECT Scope
         , SUM(CASE Val WHEN '1' THEN '1' ELSE CAST(Val AS INT) END) AS One 
         , SUM(CASE Val WHEN '0' THEN '0' ELSE CAST(Val AS INT) END) AS Zero
    FROM #tem 
    GROUP BY Scope
    
    --Query 3. Results are not accurate
    SELECT Scope
         , CAST(CASE Val WHEN '1' THEN '1' ELSE '' END AS INT)One
         , CAST(CASE Val WHEN '0' THEN '0' ELSE '' END AS INT)Zero
    FROM #tem
    GROUP BY Scope
           , Val
    

    None of these queries produced to desired results set. 

    Please help refine the queries to return the right results. Thank you

     

    Zionlite

    Monday, November 04, 2013 9:53 PM

Answers

  • Hi Yookos,

    Refer the below query, we can simply achieve using PIVOT.

    SELECT * FROM #tem
    -------------------------- final query
    SELECT Scope,[0],[1] FROM (SELECT Scope,Val FROM #tem) X
    PIVOT
    (
     COUNT(Val) FOR Val IN ([0],[1])
    ) pvt


    Regards, RSingh

    • Marked as answer by Yookos Tuesday, November 05, 2013 9:10 AM
    Tuesday, November 05, 2013 7:18 AM

All replies

  • Not sure exactly what you are looking for but perhaps

    SELECT Scope
         , CASE Val WHEN '1' THEN '1' ELSE '' END AS One
         , CASE Val WHEN '0' THEN '0' ELSE '' END AS Zero
    FROM #tem

    If that's not it, could you show us the result you are looking for from that sample data.

    Tom

    P.S.  Thanks for providing the sample tables and data.  That is always very helpful.

    Monday, November 04, 2013 10:05 PM
  • SELECT Scope
         , SUM(CASE Val WHEN '1' THEN  1 Else 0  END) AS One 
         , SUM(CASE Val WHEN '0' THEN  1 Else 0 END) AS Zero
    FROM #tem 
    GROUP BY Scope

    Monday, November 04, 2013 10:21 PM
    Moderator
  • In your data, Raff has an empty string, with is converting to a 0. Is this what you want, or do you want a NULL value so he does not count?


    Chuck


    Monday, November 04, 2013 11:42 PM
  • I hope this is not anything like the real data. We never use IDENTITY for anything, much less a key. When I see it in real code, I know that the programmer believes in Kabbalah magic, not RDBMS. I also hope the real data element names are ISO-11179 standards.

    CASE is an expression and not a statement. 

    But your real problem is that the proprietary BIT data type is numeric! All data types in SQL are NULL-able by definition, too. And we never use BIT is SQL as a flag. So your NULL-able flag is both wrong and bad design. Why are using strings to load that columns? 

    Let's try again: 

    CREATE TABLE Tem
    (kabbalah INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY, 
     something_something_scope NVARCHAR(30) NOT NULL, 
     pro NVARCHAR(30), 
     silly_flg BIT DEFAULT 0);

    Here is ANSI/ISO syntax for INSERT INTO, so you can stop using the 1970's Sybase syntax. 


    INSERT INTO Tem
    VALUES
    ('Johnson', 'Pad', 0), 
      ('Mark', 'Shoes', 0), 
      ('Raff', 'Wood', NULL),  --- Blank is not a number. 
      ('Carl', 'Yam', 1),  
      ('Carl', 'Ball', 1),  
      ('Mark', 'Ball', 1), 
      ('Johnson', 'Bag', 1),  
      ('Carl', 'Racket', 1), 
      ('Carl', 'PIN', 1), 
      ('Carl', 'Ball', 0), 
      ('Mark', 'Tie', 0), 
      ('Mark', 'Pin', 0); 

    >> --Query 1. Good result when no Group By Clause is applied
    SELECT something_scope, 
           CASE silly_flg WHEN 1 THEN '1' ELSE '' END AS one_strinf,
           CASE silly_flg WHEN 0 THEN '0' ELSE '' END AS zero_string
    FROM Tem; <<

    Your data types are a mess of strings and numeric in this query. It makes no sense. I re-wrote the bits as numeric to save casting. 

    --Query 2. Not the desired result as GROUP BY Clause is in use
    SELECT something_scope,
          SUM(CASE silly_flg WHEN 1 THEN 1 ELSE CAST(silly_flg AS INTEGER) END) AS one_tot,
         SUM(CASE silly_flg WHEN 0 THEN 0 ELSE CAST(silly_flg AS INTEGER) END) AS zero_tot
    FROM Tem 
    GROUP BY something_scope;

    --Query 3. Results are not accurate
    SELECT something_scope,
           CAST(CASE silly_flg WHEN 1 THEN 1 
                ELSE NULL END AS INTEGER) AS one_int,
           CAST(CASE silly_flg WHEN 0 THEN 0
                ELSE NULL END AS INTEGER)AS zero_int
    FROM Tem
    GROUP BY something_scope, silly_flg;

    What did you expect to happen? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 05, 2013 12:42 AM
  • Thanks for the correction. I am using 2005 version of SQL. The table was not designed by me. The example given is a true reflection of what the table is like. 

    I want to see a summation or count of Val separeted into Zero andOne columns against a given name.

    A result that looks like this:

    Name One Zero

    Carl 4 1

    Johnson 1 1

    Mark 1  3

    Raff Null 1

    This what we hope to accomplish. 

    Thanks for the help


    Zionlite


    • Edited by Yookos Tuesday, November 05, 2013 6:25 AM 55
    Tuesday, November 05, 2013 6:24 AM
  • I want to see the summation or count of Val that in separate groups -Zero and One that sums what Carl has under One and under Zero. If you ran the query you would see that Carl has a count of 1 four times and zero once. Therefore the output should look like:

    Name One Zero

    Carl  4  1

    Johnson  1  1

    Mark   3

    Raff  Null  1

    Thank you


    Zionlite




    • Edited by Yookos Tuesday, November 05, 2013 6:35 AM
    Tuesday, November 05, 2013 6:33 AM
  • Hi Yookos,

    Refer the below query, we can simply achieve using PIVOT.

    SELECT * FROM #tem
    -------------------------- final query
    SELECT Scope,[0],[1] FROM (SELECT Scope,Val FROM #tem) X
    PIVOT
    (
     COUNT(Val) FOR Val IN ([0],[1])
    ) pvt


    Regards, RSingh

    • Marked as answer by Yookos Tuesday, November 05, 2013 9:10 AM
    Tuesday, November 05, 2013 7:18 AM
  • You are the best! Very accurate. Thank you

    Zionlite

    Tuesday, November 05, 2013 9:10 AM