aggregate functions not allowed in where clause

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.

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.

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.

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

Sorry, I'm trying very hard but can't understand this:

SELECT     deptno
,          COUNT (*)  AS n_clerks
FROM       scott.emp
WHERE      job  = 'CLERK'
GROUP BY   deptno
;

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?