Skip to Main Content

aggregate functions not allowed in where clause

RobeenNov 17 2021 — edited Nov 17 2021

Oracle DB 12.1.0.2
AIX
Hello Team,
can you please advise how I can use aggregate functions with WHERE clause?

SELECT DS_SUB.adjusted_cost FROM FA.FA_DEPRN_SUMMARY DS_SUB,FA.FA_DEPRN_DETAIL DD,FA.FA_DEPRN_SUMMARY DS
HAVING DS.PERIOD_COUNTER =NVL (MIN (DS_SUB.PERIOD_COUNTER), DD.PERIOD_COUNTER)
GROUP BY DS_SUB.adjusted_cost,DS_SUB.PERIOD_COUNTER,DD.PERIOD_COUNTER;
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:

Please find attached sample data:

Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,17503.96);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,423294.71);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,184594.53);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,30623.15);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,95658.2);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9202.08);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,152863.5);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,88691.1);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,36324);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,222989);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,109947.5);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,6410.93);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,105083.57);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,222989);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,235253.4);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,11583.32);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,11583.32);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,4136.9);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,4136.9);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9928.56);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9928.56);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,140355.94);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,87722.46);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,298256.36);
Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Thanks,
Roshan

This post has been answered by Frank Kulash on Nov 17 2021
Jump to Answer
Comments
Post Details
Added on Nov 17 2021
18 comments
1,848 views