ORACLE sum inside a case statement

Question

Hi I need the result of this. so if a entityID matches to a value I need the sum of certain column.I am getting an expression missing error. Can someone point me to where the error is? Thanks.

                   SELECT                               
                            p.jobTitle,
                            p.department,
                            p.person,
                            ufr.meets,
                            ufr.exceeds,
                            CASE 
                                 WHEN ufr.entityid = 'AHT' THEN (AD.acdcalls + AD.daacdcalls) 
                                 WHEN ufr.entityid = 'ACW' THEN (AD.acdcalls + AD.daacdcalls) 
                                 WHEN ufr.entityid = 'Adherence' THEN SUM(AA.totalSched)
                                 WHEN ufr.entityid = 'Conformance' THEN SUM(AS.minutes)
                                 ELSE null
                            END as weight,
                            (weight * meets) AS weightedMeets,
                            (weight * exceeds) AS weightedExceeds

                    FROM  M_PERSON p
                    JOIN A_TMP5408_UNFLTRDRESULTSAG ufr
                            ON ufr.department = p.department AND ufr.jobTitle = p.jobTitle 
                    LEFT JOIN M_AvayaDAgentChunk AD 
                            ON AD.person = p.person and ufr.split = AD.split    
                    LEFT JOIN M_AgentAdherenceChunk AA 
                            ON AA.person = p.person 
                    LEFT JOIN M_AgentScheduleChunk AS 
                            ON AS.person = p.person 

                    GROUP BY
                            p.person,
                            p.department,
                            p.jobTitle,
                            ufr.meets,
                            ufr.exceeds,
                            weight,
                            weightedMeets,
                            weightedExceeds

Answer 1

In addition to the alias issue identified by Gordon, I think you'll find you need to use an aggregate function in all the THEN clauses of your CASE statement, and that you need to GROUP BY ufr.entityid as well. Otherwise you'll start getting ora-00979 errors (not a GROUP BY expression). If you don't want the aggregate function in all clauses, then you'll have to group by the expressions you're summing as well.

Small illustration:

CREATE TABLE tt (ID varchar2(32), sub_id varchar2(32), x NUMBER, y NUMBER);
INSERT INTO tt VALUES ('ID1', 'A', 1, 6);
INSERT INTO tt VALUES ('ID1', 'B', 1, 7);
INSERT INTO tt VALUES ('ID2', 'A', 2, 6);
INSERT INTO tt VALUES ('ID2', 'B', 2, 7);
INSERT INTO tt VALUES ('ID3', 'A', 3, 6);
INSERT INTO tt VALUES ('ID3', 'B', 3, 7);
INSERT INTO tt VALUES ('ID3', 'C', 3, 8);

SELECT ID, CASE WHEN sub_id = 'A' THEN SUM(y)
                WHEN sub_id = 'B' THEN SUM(x)
                ELSE (x + y) END tst
  FROM tt
 GROUP BY ID

ORA-00979: not a GROUP BY expression  (points at sub_id in WHEN)

SELECT ID, CASE WHEN sub_id = 'A' THEN SUM(y)
                WHEN sub_id = 'B' THEN SUM(x)
                ELSE (x + y) END tst
  FROM tt
 GROUP BY ID, sub_id

ORA-00979: not a GROUP BY expression    (points at x in ELSE)

SQL> SELECT ID, CASE WHEN sub_id = 'A' THEN SUM(y)
  2                  WHEN sub_id = 'B' THEN SUM(x)
  3                  ELSE SUM(x + y) END tst
  4    FROM tt
  5   GROUP BY ID, sub_id;

ID                                      TST
-------------------------------- ----------
ID1                                       6
ID3                                       6
ID3                                       3
ID1                                       1
ID2                                       6
ID2                                       2
ID3                                      11

Answer 2

As well as the issues mentioned by @GordonLinoff (that AS is a keyword) and @DCookie (you need entityid in the group-by):

• you also need acdcalls and daacdcalls in the group-by (unless you can aggregate those);
• you can't refer to a column alias in the same level of query, so (weight * meets) AS weightedMeets isn't allowed - you've just define what weight is, in the same select list. You need to use an inline view, or a CTE, if you don't want to repeat the case logic.

I think this does what you want:

SELECT
        jobTitle,
        department,
        person,
        meets,
        exceeds,
        weight,  
        (weight * meets) AS weightedMeets,
        (weight * exceeds) AS weightedExceeds
FROM
(
        SELECT                               
                MP.jobTitle,
                MP.department,
                MP.person,
                ufr.meets,
                ufr.exceeds,
                CASE 
                     WHEN ufr.entityid = 'AHT' THEN (MADAC.acdcalls + MADAC.daacdcalls) 
                     WHEN ufr.entityid = 'ACW' THEN (MADAC.acdcalls + MADAC.daacdcalls) 
                     WHEN ufr.entityid = 'Adherence' THEN SUM(MAAC.totalSched)
                     WHEN ufr.entityid = 'Conformance' THEN SUM(MASC.minutes)
                     ELSE null
                END as weight
        FROM  M_PERSON MP
        JOIN A_TMP5408_UNFLTRDRESULTSAG ufr
                ON ufr.department = MP.department AND ufr.jobTitle = MP.jobTitle 
        LEFT JOIN M_AvayaDAgentChunk MADAC 
                ON MADAC.person = MP.person and ufr.split = MADAC.split    
        LEFT JOIN M_AgentAdherenceChunk MAAC
                ON MAAC.person = MP.person 
        LEFT JOIN M_AgentScheduleChunk MASC
                ON MASC.person = MP.person 
        GROUP BY
                MP.person,
                MP.department,
                MP.jobTitle,
                ufr.meets,
                ufr.exceeds,
                ufr.entityid,
                MADAC.acdcalls,
                MADAC.daacdcalls
);

Your fist two case branches could be combined since the calculation is the same, but will work either way.