CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Piston
Piston

Reputation: 95

Group rows when using case expresion

I have the folloring query, that give me the next result:

select OBJ_POSITION,

CASE 
    WHEN (EVT_JOBTYPE = 'PM' and EVT_DESC like '%TMPL%') THEN (SUM(CONVERT(float, ACT_EST)))
    ELSE '0'
END as HORAS_TMPL,

CASE 
    WHEN (EVT_JOBTYPE = 'CMS') THEN (SUM(CONVERT(float, ACT_EST)))
    ELSE '0'
END as HORAS_SUST,

CASE 
    WHEN ((EVT_JOBTYPE = 'PM' and EVT_DESC like '%TMPL%') or (EVT_JOBTYPE = 'CMS')) THEN (SUM(CONVERT(float, ACT_EST)))
    ELSE '0'
END as TOTAL_HORAS

from R5ACTIVITIES inner join R5EVENTS on ACT_EVENT = EVT_CODE inner join R5OBJECTS on EVT_OBJECT = OBJ_CODE    
where OBJ_ORG = '156' and OBJ_POSITION like '156-EBM%' and EVT_STATUS = 'R' and EVT_TYPE in ('JOB', 'PPM')    
group by OBJ_POSITION, EVT_JOBTYPE, EVT_DESC


OBJ_POSITION  / HORAS_TMPL / HORAS_SUST / TOTAL_HORAS   
156-EBM01           0            0           0
156-EBM01           0            0           0
156-EBM01           0            0           0
156-EBM02           0            0           0
156-EBM02           0            0           0
156-EBM03           0            0           0
156-EBM04           0            0           0
156-EBM04           8            0           8
156-EBM04           0.5          0           0.5
156-EBM04           0.75         0           0.75
156-EBM04           0            1.5         1.5
156-EBM05           0            0           0
156-EBM05           0            0           0
156-EBM05           0            0           0

The information is correct, but I only want one row per OBJ_Position, doing the sum of it's numbers (ACT_EST), like this:

OBJ_POSITION  / HORAS_TMPL / HORAS_SUST / TOTAL_HORAS   
156-EBM01           0            0           0
156-EBM02           0            0           0
156-EBM03           0            0           0
156-EBM04           9.25         1.5         10.75
156-EBM05           0            0           0

I've tried a lots of group by but none seems to work. I think removing EVT_JOBTYPE from group by may work, but then it throws an error because its not an aggregate funcion. Same with EVT_DESC...

Upvotes: 1

Views: 31

Answers (1)

Sahi
Sahi

Reputation: 1484

is this helpful.?

    ;with cte
    As
    (
        select OBJ_POSITION,

        CASE 
            WHEN (EVT_JOBTYPE = 'PM' and EVT_DESC like '%TMPL%') THEN (SUM(CONVERT(float, ACT_EST)))
            ELSE '0'
        END as HORAS_TMPL,

        CASE 
            WHEN (EVT_JOBTYPE = 'CMS') THEN (SUM(CONVERT(float, ACT_EST)))
            ELSE '0'
        END as HORAS_SUST,

        CASE 
            WHEN ((EVT_JOBTYPE = 'PM' and EVT_DESC like '%TMPL%') or (EVT_JOBTYPE = 'CMS')) THEN (SUM(CONVERT(float, ACT_EST)))
            ELSE '0'
        END as TOTAL_HORAS

        from R5ACTIVITIES inner join R5EVENTS on ACT_EVENT = EVT_CODE inner join R5OBJECTS on EVT_OBJECT = OBJ_CODE    
        where OBJ_ORG = '156' and OBJ_POSITION like '156-EBM%' and EVT_STATUS = 'R' and EVT_TYPE in ('JOB', 'PPM')    
        group by OBJ_POSITION, EVT_JOBTYPE, EVT_DESC

    )

    Select OBJ_POSITION, SUM(HORAS_TMPL) as HORAS_TMPL,SUM(HORAS_SUST) as HORAS_SUST,SUM(TOTAL_HORAS) as TOTAL_HORAS
    Group by OBJ_POSITION

Upvotes: 1

Related Questions