Not a singlenot a single-group group function ORA-00937: Oracle-CodePudding

What can I do at the highest level to change this error
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause:
*Action: Error at Line: 3 Column: 5

     select
       year,
       Net_TWRR_PERIOD,
     round(((CASE WHEN MOD(SUM(CASE WHEN     
(   Net_TWRR_PERIOD ) <0 then 1 else 0  end ), 2 )=1                                                       THEN -1 ELSE 1 END *    EXP(SUM(LN(ABS(Net_TWRR_PERIOD)))))-1)*100,2)

    from (select
      year,

       round(((CASE WHEN MOD(SUM(CASE WHEN (Net_TWRR ) <0 then 1 else 0 end ), 2 )=1    THEN -1          ELSE 1 END *    EXP(SUM(LN(ABS(Net_TWRR)))))-1)*100,2) as Net_TWRR_PERIOD
      from

       (select ( net_rate_of_return / 100   1) as Net_TWRR,

     year

     from eom
    WHERE   id =  '2'
                       and start_date < '09-September-2022'
      ) group by year order by year)

CodePudding user response：

You have the outer query:

select year,
       Net_TWRR_PERIOD,
       round(
        (
          CASE
          WHEN MOD(SUM(CASE WHEN Net_TWRR_PERIOD < 0 then 1 else 0 end ), 2)=1
          THEN -1
          ELSE 1
          END
          * EXP(SUM(LN(ABS(Net_TWRR_PERIOD))))
          -1
        ) * 100,
        2
      )
from  ( ... )

Which has a mix of aggregated columns and non-aggregated columns and you do not have a GROUP BY clause (in that outer query). You need to make sure all columns are either aggregated or contained in a GROUP BY.

So, change the outer query to:

select year,
       Net_TWRR_PERIOD,
       round(
        (
          CASE
          WHEN MOD(SUM(CASE WHEN Net_TWRR_PERIOD < 0 then 1 else 0 end ), 2)=1
          THEN -1
          ELSE 1
          END
          * EXP(SUM(LN(ABS(Net_TWRR_PERIOD))))
          -1
        ) * 100,
        2
      )
from  ( ... )
GROUP BY year, Net_TWRR_PERIOD

CodePudding user response：

you are using the SUM functiion and the GROUP BY is missing in the outermost SQL.

    create  table eom(year number(4),  start_date date,  net_rate_of_return number (10,4), id number(4))
    
  SELECT year,
         Net_TWRR_PERIOD,
         ROUND (
              (  (  CASE
                       WHEN MOD (
                               SUM (
                                  CASE
                                     WHEN (Net_TWRR_PERIOD) < 0 THEN 1
                                     ELSE 0
                                  END),
                               2) = 1
                       THEN
                          -1
                       ELSE
                          1
                    END
                  * EXP (SUM (LN (ABS (Net_TWRR_PERIOD)))))
               - 1)
            * 100,
            2)
    FROM (  SELECT year,
                   ROUND (
                        (  (  CASE
                                 WHEN MOD (
                                         SUM (
                                            CASE
                                               WHEN (Net_TWRR) < 0 THEN 1
                                               ELSE 0
                                            END),
                                         2) = 1
                                 THEN
                                    -1
                                 ELSE
                                    1
                              END
                            * EXP (SUM (LN (ABS (Net_TWRR)))))
                         - 1)
                      * 100,
                      2)
                      AS Net_TWRR_PERIOD
              FROM (SELECT (net_rate_of_return / 100   1) AS Net_TWRR, year
                      FROM eom
                     WHERE id = '2' AND start_date < '09-September-2022')
          GROUP BY year
          ORDER BY year, Net_TWRR_PERIOD)
GROUP BY year, Net_TWRR_PERIOD