Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979: not a GROUP BY expression
ORA-00979: not a GROUP BY expression [message #261854] Thu, 23 August 2007 15:49 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have a query as follows:-

SELECT  
      TO_CHAR(RECORD_DATE,'YYYY-MM-DD') AS "NAME",   
      PORTFOLIO_ID  AS "PORTFOLIO_ID", 
      MAX(TOTAL_MV) AS "MARKETVALUE", 
      --DISTRESSED_CAT,  
      MAX(TOTAL_SV) AS "STATVALUE", 
      MAX(TOTAL_GV) AS "GAAPVALUE", 
      MAX(TOTAL_PAR) AS "PAR",  
      COUNT(1) OVER (PARTITION BY 1) AS TOT_ROWS,  
      (
        SELECT 
          SUM( STATVALUE  )  
        FROM 
          WATCHLIST_VIEW INNER 
        WHERE  
          INNER.RECORD_DATE = OUTER.RECORD_DATE AND  
          INNER.PORTFOLIO_ID = OUTER.PORTFOLIO_ID  
        HAVING 
          SUM(MARKETVALUE)>0  ) AS "COUNT", 
          CCMSECTYPE AS "NAME_CCM",  
          SUM(MARKETVALUE) AS "MARKETVALUE_CCM", 
          SUM(STATVALUE) AS "STATVALUE_CCM",  
          SUM(GAAPVALUE) AS "GAAPVALUE_CCM", 
          SUM(PAR) AS "PAR_CCM",  
          SUM( STATVALUE  
      ) AS "COUNT_CCM"  
    FROM 
      WATCHLIST_VIEW OUTER 
    WHERE 
      DISTRESSED_CAT IN (1,2,3,4) 
    GROUP BY RECORD_DATE, PORTFOLIO_ID, CCMSECTYPE 
    HAVING SUM(MARKETVALUE)>0  
    ORDER BY RECORD_DATE,CCMSECTYPE


I want to add DISTRESSED_CAT in the select statement but without adding in Group BY, since if I do that it gives me a lot od extra records in the result set which are not required. and if I dont I get "ORA-00979: not a GROUP BY expression". Is their a work around?
Re: ORA-00979: not a GROUP BY expression [message #261856 is a reply to message #261854] Thu, 23 August 2007 18:26 Go to previous messageGo to next message
mark918
Messages: 3
Registered: April 2007
Location: Seattle, WA, US
Junior Member
Well, distressed_cat can be 1,2,3, or 4. So at most you would have four times as many records if you include it in the group by. You say the extra records are not required, but the only difference between those records and the ones you are getting now is the field that you say you want to add to your query. You either need it, and you will get the extra rows, or you don't and you can suppress them. I don't see how, logically, you can do both things at the same time.
Re: ORA-00979: not a GROUP BY expression [message #262105 is a reply to message #261856] Fri, 24 August 2007 09:14 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
mark918 wrote on Thu, 23 August 2007 18:26
Well, distressed_cat can be 1,2,3, or 4. So at most you would have four times as many records if you include it in the group by. You say the extra records are not required, but the only difference between those records and the ones you are getting now is the field that you say you want to add to your query. You either need it, and you will get the extra rows, or you don't and you can suppress them. I don't see how, logically, you can do both things at the same time.



Thats exactly the question, is there a way for me to add the field without adding it in group by. Otherwise I would have to run four queries separately one with each value and that would definitely affect the performance of the app.
Re: ORA-00979: not a GROUP BY expression [message #262113 is a reply to message #261854] Fri, 24 August 2007 10:07 Go to previous messageGo to next message
mark918
Messages: 3
Registered: April 2007
Location: Seattle, WA, US
Junior Member
But the field has four different values, which of them would you like to display?
Re: ORA-00979: not a GROUP BY expression [message #262158 is a reply to message #262113] Fri, 24 August 2007 13:48 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
The point mark918 is making is that due to the way the GROUP BY clause is implemented in Oracle, it is not possible to mix single row data with aggregate functions without the GROUP BY clause. Whenever aggregate functions are mixed with scalar values there must be a GROUP BY clause for each scalar value.

I think you are looking for analytic functions as your ultimate solution.

Here's a simple example:
CREATE TABLE watchlist
 ( portfolio_id   NUMBER(3)
 , distressed_cat NUMBER(1)
 , value          NUMBER(4,2) );
 
 
INSERT INTO watchlist VALUES (305, 3, 9.99);
INSERT INTO watchlist VALUES (307, 4, 12.79);
INSERT INTO watchlist VALUES (303, 4, 8.99);
INSERT INTO watchlist VALUES (305, 4, 14.99);
INSERT INTO watchlist VALUES (307, 3, 8.50);
INSERT INTO watchlist VALUES (305, 2, 3.99);
INSERT INTO watchlist VALUES (302, 1, 1.79);
INSERT INTO watchlist VALUES (307, 3, 16.25);
INSERT INTO watchlist VALUES (305, 2, 7.45);

SELECT * FROM watchlist;

PORTFOLIO_ID  DISTRESSED_ID       VALUE
------------  -------------  ----------
         305              3        9.99
         307              4       12.79
         303              4        8.99
         305              4       14.99
         307              3         8.5
         305              2        3.99
         302              1        1.79
         307              3       16.25
         305              2        7.45

Given the data above, summary information can be obtained such as largest VALUE.
SELECT MAX(value) FROM watchlist;

MAX(VALUE)
----------
     16.25

Or, largest VALUE within each PORTFOLIO_ID.
SELECT MAX(value) FROM watchlist GROUP BY portfolio_id;

MAX(value)
----------
      8.99
     16.25
     14.99
      1.79

If I want to add any row level scalar values, those values must also be included in the GROUP BY.
SELECT portfolio_id, distressed_cat, MAX(value) 
FROM watchlist 
GROUP BY portfolio_id, distressed_cat;

   PORTFOLIO_ID DISTRESSED_CAT MAX(VALUE)
   ------------ -------------- ----------
1)          305              2       7.45
2)          305              3       9.99
3)          307              3      16.25
4)          302              1       1.79
5)          303              4       8.99
6)          307              4      12.79
7)          305              4      14.99

Of course this changes the intent of the query - now the query returns the biggest VALUE for each combination of PORTFOLIO_ID and DISTRESSED_CAT. The last point from mark918 is that rows such as 1, 2, and 7 each share the same PORTFOLIO_ID value but they each have a different DISTRESSED_CAT value; therefore a row for each must be DISTRESSED_CAT value must be returned.

I believe you would like to find MAX(value) within each group of PORTFOLIO_ID so let's take a look at the use of analytic functions
SELECT portfolio_id, distressed_cat, MAX(value) OVER() 
FROM watchlist;

PORTFOLIO_ID DISTRESSED_CAT MAX(VALUE)OVER()
------------ -------------- ----------------
         305              3            16.25
         307              4            16.25
         303              4            16.25
         305              4            16.25
         307              3            16.25
         305              2            16.25
         302              1            16.25
         307              3            16.25
         305              2            16.25

This query is returning row level information along with a summary (from the MAX analytic function). It can be used to compare the row level VALUE to the overall largest VALUE in the table. By the way, your query referenced PARTITION BY 1, which is the same as the empty ().

To identify the largest VALUE within a group, use the PARTITION BY clause to indicate the grouping (similar to a GROUP BY clause).
SELECT portfolio_id, distressed_cat, MAX(value) OVER(PARTITION BY portfolio_id) 
FROM watchlist;

PORTFOLIO_ID DISTRESSED_CAT MAX(VALUE)OVER(PARTITIONBYPORTFOLIO_ID)
------------ -------------- ---------------------------------------
         302              1                                    1.79
         303              4                                    8.99
         305              3                                   14.99
         305              4                                   14.99
         305              2                                   14.99
         305              2                                   14.99
         307              3                                   16.25
         307              4                                   16.25
         307              3                                   16.25

And now you can remove duplicate values with a DISTINCT
SELECT DISTINCT portfolio_id, distressed_cat, MAX(value) OVER(PARTITION BY portfolio_id) 
FROM watchlist;

PORTFOLIO_ID DISTRESSED_CAT MAX(VALUE)OVER(PARTITIONBYPORTFOLIO_ID)
------------ -------------- ---------------------------------------
         307              3                                   16.25
         302              1                                    1.79
         305              3                                   14.99
         305              4                                   14.99
         305              2                                   14.99
         307              4                                   16.25
         303              4                                    8.99

I hope I understood your needs and was able to clarify the situation.
Previous Topic: ORA-00942 table or view does not exist
Next Topic: Paging Row selection retention & ORA-14450 (merged)
Goto Forum:
  


Current Time: Tue Dec 06 14:14:45 CST 2016

Total time taken to generate the page: 0.12460 seconds