Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Arpad Kiss

I think you wanted DESC, not DEPT.
DESC tablename;

Pictures are worth many words, show us

image.png

user527460

Hi, @thatjeffsmith-oracle ,
Here is the picture from SQL Developer version 21.4.3.x,
Picklist is not showing with table alias 'a' as shown here.

image.png

That query to get the column list might take a half-second, be patient
Windows 10/21.4.3
image.png

user527460

hi @thatjeffsmith-oracle ,
No luck, i waited for a min but pick list not showing. I tried to change theme to dark as showing in your image, still no pick list pops up.

ask for it multiple times, if the query takes too long, we give up

Robert Kilmer

Try Ctrl-Space.

670554

Ctrl-Space is not working in Version 22.2.1.234

it brings up a different menu.

thatJeffSmith-Oracle

23.1.1 is latest and this is your example in 23.1.1

1 - 9

Post Details

Added on Nov 17 2021
18 comments
3,622 views