CODEWITHSUNDEEP
sql-serversumcaseaggregate
YelizavetaYR
YelizavetaYR

Reputation: 1701

SQL Server : Case Statement Sum

I have the following nested query 

select 
    sum(c.seats) as countPerfs, b.program, b.Prog_id
from        
    #LT_TEMP TableP
join        
    C_PROGRAM b on TableP.program_id = b.Prog_id
join        
    lt_data c on b.Program = c.program
join        
    LTR_BUDGET_REF a on TableP.program_id = a.ProgramID 
where       
    a.Participants = 'Y'
    and fyear = 2016
group by    
    b.Program, b.Prog_id

It returns output that looks like this:

program_id  season  program performance workshops   artists_sess    participants    audience
6           2016    LU:     NULL       NULL         NULL            NULL            NULL
7           2016    NC:     NULL       NULL         NULL            NULL            NULL
11          2016    AC:     NULL       NULL         NULL            NULL            NULL
12          2016    PD:     NULL       NULL         NULL            NULL            NULL
19          2016    MC:     NULL       NULL         NULL            NULL            NULL

Each program has different logic -- so If I want to calculate lets say participants. But when the program is MC we only want to return/sum certain data. But when the program Is LU we want to calculate all the data. and so on. How do I build that case statement.

for example if the program is MC we want to sum c.seats (from lt_data) but only where price is = 800. But if the program is LU we want to sum seats for all prices. if the program is PD we want to sum where the price is 800, 500 and 300. Things like that. Its a case by case basis but I'm not sure how to structure it.

Thanks in advance.

This is the full query 

UPDATE  
        #LT_TEMP 
SET     
        program = h.Program,
        participants =  h.countPerfs
FROM        
(
                select sum(c.seats) as countPerfs, b.program, b.Prog_id
                from        #LT_TEMP TableP
                join        C_PROGRAM b on TableP.program_id = b.Prog_id
                join        lt_data c on b.Program = c.program
                join        LTR_BUDGET_REF a on TableP.program_id = a.ProgramID 
                where       a.Participants = 'Y'
                and         fyear = 2016
                group by    b.Program, b.Prog_id
) h 
where h.Prog_id = #LT_TEMP.program_id
and h.Prog_id not in (27, 28, 29)

Upvotes: 3

Views: 987

Answers (1)

Beth
Beth

Reputation: 9607

I think you're trying to do your aggregation and CASE logic at too high a level. Try doing it in a subquery of lt_data grouped by program:

SELECT 
    c.Prog_id,
    fyear AS season,
    c.program, 
    SUM(l.seats) AS countPerfs
FROM        
    #LT_TEMP t JOIN        
    C_PROGRAM c ON 
    t.program_id = c.Prog_id JOIN        
    (SELECT
        fyear,
        program,
        SUM(CASE 
            WHEN program = 'mc:' AND price = 800 THEN seats 
            WHEN program = 'lu:' THEN seats
            WHEN program = 'pd:' and price in (300, 500, 800) then seats 
            ELSE seats
        END) AS calc
    FROM
        lt_data
    GROUP BY
        fyear, program) l ON 
    c.Program = l.program JOIN        
    LTR_BUDGET_REF b ON 
    TableP.program_id = b.ProgramID 
WHERE       
    b.Participants = 'Y'     AND 
    fyear = @fyear
GROUP BY  
    c.Prog_id,  
    fyear,
    c.Program

The relevant syntax example in MS's definition is: Searched CASE expression:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Upvotes: 2

Related Questions