Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
aggregate functions not allowed in where clause

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
Best Answer
-
Hi, @Robeen
One more time: Explain what you're trying to do. You've posted some examples of queries that (apparently) do it, but you still haven't explained exactly what that is. What does each row in the desired result set represent? How do you figure the values in each column?
It sounds like you're trying to use some front-end tool that doesn't support sub-queries, or at least certain types of sub-queries that you want to use. That's a shame: front-end tools are supposed to add functionality to Oracle, not remove it. Anyway, it seems like anything you want to do using a sub-query can be done with a view in the back end. Create a view using sub-queries or whatever other features Oracle supports in the database; then, in the front end simply say
SELECT * FROM the_view;
Answers
-
What are you actually trying to achieve?
You have no where conditions (or ANSI systax ON conditions, which would be preferable) joining your tables, so it's all cartesian products. In which case, what's the point of the NVL around the MIN aggregate function in the having clause, as there will always be a period_counter from ds_sub (providing it has data of course).
It looks like you're trying to implement joins with the having clause... which is an odd way to do things.
(you also failed to give us the create table statements)
-
Hi, @Robeen
Thanks for posting the INSERT statements. Don't forget to post the CREATE TABLE statement, the desired results from that sample data, an explanation of how you get those results, and you full Oracle version number.
can you please advise how I can use aggregate functions with WHERE clause?
If you want to use a WHERE clause in a query that has aggregate functions, it goes right after the FROM clause, and before the GROUP BY clause (if there is one), like this:
SELECT deptno , COUNT (*) AS n_clerks FROM scott.emp WHERE job = 'CLERK' GROUP BY deptno ;
The WHERE clause is applied before aggregate functions are computed (that is, aggregate functions are computed only on the rows that meet the conditions in the WHERE clause), so it's impossible to use an aggregate function in a WHERE clause. You can sometimes get around that by using an aggregate function in a sub-query, and use the value it returns in the WHERE clause of a super-query.
-
This article explain the order of each clause of SELECT sentence, may be worth reading to understand better how a SELECT is executed:
https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/
-
It's not even possible to try and determine what you're trying to achieve from the query you've posted. I can take a wild guess and think that you're perhaps trying to get the minimum adjusted_cost for a period_counter (looks like that is some sort of record key).
You could do that for each of the summary records...
e.g. (I've adjusted the data to have different period_counter values as all your "detail" table was just full of duplicates, which surely can't be illustrative of what you're trying to do.)
with FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) as ( select 24139,17503.96 from dual union all select 24139,423294.71 from dual union all select 24140,184594.53 from dual union all select 24140,30623.15 from dual union all select 24140,95658.2 from dual union all select 24141,9202.08 from dual union all select 24141,152863.5 from dual union all select 24141,88691.1 from dual union all select 24141,36324 from dual union all select 24142,222989 from dual union all select 24142,109947.5 from dual union all select 24143,6410.93 from dual union all select 24144,105083.57 from dual union all select 24144,222989 from dual union all select 24145,235253.4 from dual union all select 24145,11583.32 from dual union all select 24145,11583.32 from dual union all select 24146,4136.9 from dual union all select 24146,4136.9 from dual union all select 24146,9928.56 from dual union all select 24146,9928.56 from dual union all select 24146,140355.94 from dual union all select 24146,87722.46 from dual union all select 24146,298256.36 from dual ) ,FA_DEPRN_DETAIL (PERIOD_COUNTER) as ( select 24139 from dual union all select 24140 from dual union all select 24141 from dual union all select 24142 from dual union all select 24143 from dual union all select 24144 from dual union all select 24145 from dual union all select 24146 from dual ) SELECT dd.period_counter ,ds.adjusted_cost ,min(ds.adjusted_cost) over (partition by dd.period_counter) as min_adjusted_cost FROM FA.FA_DEPRN_DETAIL DD join FA.FA_DEPRN_SUMMARY DS on (dd.period_counter = ds.period_counter) / PERIOD_COUNTER ADJUSTED_COST MIN_ADJUSTED_COST -------------- ------------- ----------------- 24139 17503.96 17503.96 24139 423294.71 17503.96 24140 184594.53 30623.15 24140 30623.15 30623.15 24140 95658.2 30623.15 24141 9202.08 9202.08 24141 36324 9202.08 24141 152863.5 9202.08 24141 88691.1 9202.08 24142 222989 109947.5 24142 109947.5 109947.5 24143 6410.93 6410.93 24144 105083.57 105083.57 24144 222989 105083.57 24145 11583.32 11583.32 24145 11583.32 11583.32 24145 235253.4 11583.32 24146 87722.46 4136.9 24146 140355.94 4136.9 24146 9928.56 4136.9 24146 9928.56 4136.9 24146 4136.9 4136.9 24146 298256.36 4136.9 24146 4136.9 4136.9 24 rows selected.
... or perhaps just the pure minimum for each period counter...
with FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) as ( select 24139,17503.96 from dual union all select 24139,423294.71 from dual union all select 24140,184594.53 from dual union all select 24140,30623.15 from dual union all select 24140,95658.2 from dual union all select 24141,9202.08 from dual union all select 24141,152863.5 from dual union all select 24141,88691.1 from dual union all select 24141,36324 from dual union all select 24142,222989 from dual union all select 24142,109947.5 from dual union all select 24143,6410.93 from dual union all select 24144,105083.57 from dual union all select 24144,222989 from dual union all select 24145,235253.4 from dual union all select 24145,11583.32 from dual union all select 24145,11583.32 from dual union all select 24146,4136.9 from dual union all select 24146,4136.9 from dual union all select 24146,9928.56 from dual union all select 24146,9928.56 from dual union all select 24146,140355.94 from dual union all select 24146,87722.46 from dual union all select 24146,298256.36 from dual ) select period_counter, min(adjusted_cost) as min_adjusted_cost from fa_deprn_summary group by period_counter / PERIOD_COUNTER MIN_ADJUSTED_COST -------------- ----------------- 24139 17503.96 24140 30623.15 24142 109947.5 24143 6410.93 24146 4136.9 24145 11583.32 24144 105083.57 24141 9202.08 8 rows selected.
... though there's no point in including the detail table in that case, unless you had other attributes on the detail you needed to show.
As I said... it's just wild guessing... which illustrates all the more why you should explain your problem properly in the first place.
-
Hi, @Robeen
It looks like your sample data has 24 rows in fa_deprn_summary and 17 rows in fa_deprn_detail, all with the same value of period_counter. Is that really the best way to test whatever you want to do? It rarely (if ever) makes sense to have all rows in a sample table have the same value in any column. Why not post sample data and desired results with a few different values?
-
Please see the original query.
SELECT FALU.MEANING ASSET_TYPE, DECODE (AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, DECODE (AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER ASSET_NUM, AD.DESCRIPTION ASSET_DESC, AD.ASSET_ID, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, NVL (DS.BONUS_RATE, 0) BONUS_RATE, DECODE ( ADJ.SOURCE_TYPE_CODE, 'CIP ADJUSTMENT', SUM ( DECODE ( th_captest.transaction_type_code, '', 0, DECODE (adj.debit_credit_flag, 'DR', 1, -1) * NVL (adj.adjustment_amount, 0))), SUM (NVL (DD.ADDITION_COST_TO_CLEAR, 0))) COST, SUM (DD.YTD_DEPRN) YTD_DEPRN, SUM (DD.DEPRN_RESERVE) DEPRN_RESERVE, TH.TRANSACTION_HEADER_ID THID, DP.PERIOD_NAME, DS.BOOK_TYPE_CODE, dp.period_OPEN_date, dp.period_close_date, xgsv.description FROM apps.FA_DEPRN_SUMMARY DS, apps.FA_ADDITIONS AD, apps.GL_CODE_COMBINATIONS DHCC, apps.FA_DISTRIBUTION_HISTORY DH, apps.FA_LOOKUPS FALU, apps.FA_CATEGORY_BOOKS CB, apps.FA_TRANSACTION_HEADERS TH, apps.FA_ASSET_HISTORY AH, apps.FA_BOOKS BKS, apps.fa_deprn_periods dp, apps.FA_DEPRN_DETAIL DD, apps.FA_ADJUSTMENTS ADJ, apps.FA_TRANSACTION_HEADERS TH_CAPTEST, XXMT_GL_SEGMENTS_V XGSV WHERE --DS.BOOK_TYPE_CODE IN ('MTL CORP BOOK', 'CLP CORP BOOK') DS.ASSET_ID = DD.ASSET_ID AND DS.PERIOD_COUNTER = (SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) FROM apps.FA_DEPRN_SUMMARY DS_SUB WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN') AND DH.ASSET_ID = DD.ASSET_ID ...
@BluShadow My goal is to create a view (instead of WITH...AS) for subquery below since I would like to test the query in Impala also given that impala does not support subqueries.
(SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) FROM apps.FA_DEPRN_SUMMARY DS_SUB WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN')
and join (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) from the view with DS.PERIOD_COUNTER.
@Frank Kulash I notice if I created a view with query below
SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) FROM apps.FA_DEPRN_SUMMARY DS_SUB,apps.FA_DEPRN_DETAIL DD WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN'
the GROUP BY CLAUSE must contain 'dd.period_counter' otherwise we will get NOT GROUP BY expression error. Why is it so? In the example you provided the GROUP BY contains
SELECT deptno , COUNT (*) AS n_clerks FROM scott.emp WHERE job = 'CLERK' GROUP BY deptno ;
fields before the function.
-
Hi, @Robeen
Once again, post CREATE TABLE statements to go with the INSERT statements you've already posted, the desired results from the given sample data, an explanation of how you get those results, and your full Oracle version.
My goal is to create a view ...
Okay, then the desired results will be the result set of
SELECT * FROM the_view;
Post the exact results you want. -
No, I wasn't asking for the justification as to WHY you want to do it (I don't care if it's to work for an Impala or an Antelope), I was asking WHAT you wanted to do i.e. the logic you're trying to implement, something like "I'm trying to find the minimum period counter in the summary table for a given <something or other> and link that to the detail table, to then get further child records from the summary table, where the child records are identified by......". Sure, if there are restrictions such as "I need to avoid using subqueries or WITH clauses" then state those.
Or something along those lines.
And, as Frank says, give us some better sample data rather than just everything with the same period counter, and show what you want the results to be. Otherwise we've just got a query that doesn't work, with data that isn't representative of what you're trying to achieve and no idea what a correct solution would actually look like.
-
Hi, @Robeen
I notice if I created a view with query below
SELECT NVL (MIN (DS_SUB.PERIOD_COUNTER), dd.period_counter) FROM apps.FA_DEPRN_SUMMARY DS_SUB,apps.FA_DEPRN_DETAIL DD WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN'
the GROUP BY CLAUSE must contain 'dd.period_counter' otherwise we will get NOT GROUP BY expression error. Why is it so?
That follows from the A-B-C's of GROUP BY.
In a query with a GROUP BY clause and/or an aggregate function, every expression if the SELECT, HAVING and ORDER BY clauses must be one of the following:
- an Aggregate function
- one of the GROUP BY expressions
- a Constant
- something that Depends Entirely on the above
In the code above, dd.period_counter (the second argument to NVL in the SELECT clause) is none of those.
-
Hi, @Robeen
Sorry, I'm trying very hard but can't understand this:
In the example you provided the GROUP BY contains
SELECT deptno , COUNT (*) AS n_clerks FROM scott.emp WHERE job = 'CLERK' GROUP BY deptno ;
fields before the function.
What do you mean by "field"? Do you mean "column"? Do you mean "expression"? Do you mean something else? If so, what?
In that query, the GROUP BY clause does not contain any function. The only function is the aggregate COUNT function in the SELECT clause. There is one other column (deptno) before the column where that function is used. Do you have some question about the deptno column?