ORA-00936: missing expression while running subquery in AVG()-CodePudding

I've got three tables, Accounts, Clients and Ops, which represent clients having some bank accounts and some operations (withdraw/deposit) into those bank accounts.

The withdraw/deposit info is decided based on the amount stored in the ops.value field (either negative or positive).

Now, if I want to compute the average value of the maximum positive operations made by people with an age < 25 and people with an age > 40: I thought about filtering first those people. This means, selecting only people with these age ranges, which have operations = maximum value stored in ops.value. Then, computing the AVG() of it.

This is the query I thought of:

SELECT AVG(
    SELECT ops.value FROM ops
    JOIN accounts
    ON accounts.cid = ops.cid
    JOIN clients
    ON clients.ssn = accounts.ssn
    WHERE ops.value = (
        SELECT MAX(ops.value) FROM ops 
    )
    AND clients.age < 25
    OR clients.age > 40
) FROM ops

The problem is, I get the following error:

Error at line 2/5: ORA-00936: missing expression

I don't think I need to mention more of the table structure, because the problem only lays in the SELECT AVG (...) part. Only writing the query inside the AVG() function will result in a proper entity set, but putting everything under AVG() seems to ruin my query.

What may be the problem, in this case?

CodePudding user response：

The initial error is because avg() expects an expression, which you can achieve by converting your subquery to an expression, by enclosing that in another layer of parentheses:

SELECT AVG(
  (
    SELECT ops.value FROM ops
    ...
    OR clients.age > 40
  )
) FROM ops

but that will get "ORA-01427: single-row subquery returns more than one row" because you'll get more rows than you expect; which is because your logic is ambiguous and needs extra parentheses:

SELECT AVG(
  (
    SELECT ops.value FROM ops
    WHERE ops.value = (
        SELECT MAX(ops.value) FROM ops 
    )
    AND (clients.age < 25
    OR clients.age > 40)
  )
) FROM ops

That will return a single value, but that is just the maximum value, because you aren't correlating to get the maximum per client. (And it will still get too-many-rows if multiple clients have the same maximum value.) And averaging a single value isn't useful.

Adding correlation isn't as simple as it looks as you need to correlate at client level.

But you don't need to do that really; you can nest aggregation, and just do:

select  avg(max(o.value))
from clients c
join accounts a on a.ssn = c.ssn
join ops o on o.cid = a.cid
where c.age < 25 or c.age > 40
group by c.ssn

fiddle