locked
Using CASE select statement RRS feed

  • Question

  • Hello,

    Current Data is as follows;

    DeductionID

    Shortname

    Amount

    Employee ID

    1

    Loans

    23

    101

    2

    W-2

    15

    102

    3

    State Levy

    5

    103

    4

    SUI Tax

    198

    104

    5

    Loans

    30

    101

    6

    W-2

    20

    102

    7

    State Levy

    10

    103

    8

    SUI Tax

    150

    104

    Can someone please help me with SQL query using CASE select statements so that the results are as follows:

    Employee ID

    Loans_Sum

    W-2_sum

    State Levy_sum

    SUI Tax_Sum

    101

    53

    35

    15

    300

    102

    103

    Thank you

    Tuesday, May 19, 2020 2:31 PM

All replies

  • CREATE TABLE mytable(
       DeductionID INTEGER  NOT NULL PRIMARY KEY 
      ,Shortname   VARCHAR(10) NOT NULL
      ,Amount      INTEGER  NOT NULL
      ,Employee_ID INTEGER  NOT NULL
    );
    INSERT INTO mytable(DeductionID,Shortname,Amount,Employee_ID) VALUES
     (1,'Loans',23,101)
    ,(2,'W-2',15,102)
    ,(3,'State Levy',5,103)
    ,(4,'SUI Tax',198,104)
    ,(5,'Loans',30,101)
    ,(6,'W-2',20,102)
    ,(7,'State Levy',10,103)
    ,(8,'SUI Tax',150,104);
    
    
    select Employee_ID 
    ,Sum(Case when Shortname='Loans' then Amount else null end) as Loans_Sum
    ,Sum(Case when Shortname='W-2' then Amount else null end) as [W-2_sum]
    ,Sum(Case when Shortname='State Levy' then Amount else null end) as [State Levy_sum]
    ,Sum(Case when Shortname='SUI Tax' then Amount else null end) as [SUI Tax_Sum]
     from mytable
    group by Employee_ID
    
    
    
    drop TABLE mytable

    • Proposed as answer by Lily Lii Thursday, May 21, 2020 8:24 AM
    Tuesday, May 19, 2020 2:51 PM
  • Thank you so much. I really appreciate. 
    Tuesday, May 19, 2020 2:52 PM
  • Another way to do this is using PIVOT:

    DECLARE  @mytable TABLE(
       DeductionID INTEGER  NOT NULL PRIMARY KEY 
      ,Shortname   VARCHAR(10) NOT NULL
      ,Amount      INTEGER  NOT NULL
      ,Employee_ID INTEGER  NOT NULL
    );
    INSERT INTO @mytable(DeductionID,Shortname,Amount,Employee_ID) VALUES
     (1,'Loans',23,101)
    ,(2,'W-2',15,102)
    ,(3,'State Levy',5,103)
    ,(4,'SUI Tax',198,104)
    ,(5,'Loans',30,101)
    ,(6,'W-2',20,102)
    ,(7,'State Levy',10,103)
    ,(8,'SUI Tax',150,104);
    
    
    select 
    	a.Employee_ID,
    	SUM(Loans) AS Loans_Sum,
    	SUM([W-2]) AS [W-2_Sum],
    	SUM([State Levy]) AS [State Levy_Sum],
    	SUM([SUI Tax]) AS [SUI Tax_Sum]
    from (
    	select *
    	from @mytable t
    PIVOT
    (	
    	SUM(amount) FOR Shortname IN 
    		([Loans],[W-2],[State Levy],[SUI Tax])
    ) as pvt
    ) a
    GROUP BY a.Employee_ID
    ORDER BY a.Employee_ID
    
    
    

    • Proposed as answer by Lily Lii Thursday, May 21, 2020 8:24 AM
    Tuesday, May 19, 2020 5:00 PM
  • Hi nash_g,

    Pivot is useful and easy to do it:

    create table #mytable(
       DeductionID INTEGER  NOT NULL PRIMARY KEY 
      ,Shortname   VARCHAR(10) NOT NULL
      ,Amount      INTEGER  NOT NULL
      ,Employee_ID INTEGER  NOT NULL
    );
    INSERT INTO #mytable(DeductionID,Shortname,Amount,Employee_ID) VALUES
     (1,'Loans',23,101)
    ,(2,'W-2',15,102)
    ,(3,'State Levy',5,103)
    ,(4,'SUI Tax',198,104)
    ,(5,'Loans',30,101)
    ,(6,'W-2',20,102)
    ,(7,'State Levy',10,103)
    ,(8,'SUI Tax',150,104);
    
    ;with cte1 as(
    select Shortname ,sum(Amount) total,Employee_ID 
    from #mytable 
    group by Shortname ,Employee_ID )
    select * from cte1 pivot(sum(total) for shortname in ([Loans],[W-2],[State Levy],[SUI Tax])) p

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Thursday, May 21, 2020 8:24 AM
    Wednesday, May 20, 2020 5:48 AM
  • Where is the DDL? Did you read the forum posting rules? Are you used to making everybody else do your work for you instead of following basic netiquette? But what you're trying to do is really, really bad programming.

    SQL and client/server computing in general is based on the idea of tiers in the architecture. One tier is the database and retrieves the data in a standard format. It then passes that data to a presentation tier, which does all the coloring and display work and all the things that you want to do for human consumption. The architecture may also pass that data to a statistical analysis program, a cell phone program or anything else. Basically you are writing a monolithic program using SQL that does everything! This is 1960s COBOL, not any modern programming technique.

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

    Wednesday, May 20, 2020 4:25 PM
  • Hi nash_g,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 21, 2020 8:24 AM