CODEWITHSUNDEEP
oracle-databasecaseaggregate-functions
MS_PDX_1985
MS_PDX_1985

Reputation: 39

ORA-00979: not a GROUP BY expression (Case statement issue)

I am getting the ORA-00979 error message on the following query. I should preface this by stating that I've read many of the discussions on this error on Stack Overflow and still cannot find a solution. I'm also confident the error is coming from the CASE statement, because when I completely removed the statement the query has yielded results, and I also received results when I removed all of the CASE statement except the 'SUM(Z.EQUITY_FOUR)' (that is, I'm also confident there is not an issue with the database EQUITY_FOUR is pulling from).

SELECT 
EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE) as Yr,
EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE) as Mth,
Q.CITY as City,
CASE WHEN pp.ISACTIVE = 'NO' 
AND EXTRACT(YEAR FROM HDS.CANCELLATION_DATE) = EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE) 
AND EXTRACT(MONTH FROM HDS.CANCELLATION_DATE) = EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE)
THEN SUM(Z.EQUITY_FOUR)
END as Cancelled

(From Statement)

(Where Statement)

GROUP BY EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE),
     EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE),
     Q.CITY,
     CASE 
     WHEN pp.ISACTIVE = 'NO' 
     AND EXTRACT(YEAR FROM HDS.CANCELLATION_DATE) = EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE) 
     AND EXTRACT(MONTH FROM HDS.CANCELLATION_DATE) = EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE)
     THEN SUM(Z.EQUITY_FOUR)
     END

Concerning the CASE statement in the Group BY clause, while doing my research I read this (ORA-00979: not a GROUP BY expression issue) post which contains two responses stating to add the complete CASE statement to the GROUP BY expression and which the author of the post claims worked for his query. I'm still getting the above-specified error, however.

Thanks for any help!

Upvotes: 2

Views: 6455

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I think you want the CASE inside the SUM():

SELECT EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE) as Yr,
       EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE) as Mth,
       Q.CITY as City,
       SUM(CASE WHEN pp.ISACTIVE = 'NO' AND
                     EXTRACT(YEAR FROM HDS.CANCELLATION_DATE) = EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE) AND
                     EXTRACT(MONTH FROM HDS.CANCELLATION_DATE) = EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE)
                THEN Z.EQUITY_FOUR
           END) as Cancelled
(From Statement)
(Where Statement)
GROUP BY EXTRACT(YEAR FROM HDS.MORTGAGE_START_DATE),
         EXTRACT(MONTH FROM HDS.MORTGAGE_START_DATE),
         Q.CITY;

Upvotes: 3

Related Questions