aggregate functions not allowed in where clause

Robeen

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

Frank Kulash

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;

BluShadow

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)

Frank Kulash

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.

L. Fernigrini

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/

BluShadow

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.

Frank Kulash

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?

Robeen

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.

Frank Kulash

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.

BluShadow

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.

Frank Kulash

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.

Frank Kulash

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?