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)
>Solution :
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