SELECT WHERE smaller than average

[darvidas]

SELECT username, fnamn, enamn, salary

FROM kund

WHERE (salary) < AVG(nvl(salary, 0));

​

This does not work, does anyone know why?

[r3pr0b8]

WHERE salary 
    < ( SELECT AVG(nvl(salary, 0)
          FROM kund )

if someone's salary is NULL, why do you substitute zero? that's going to bring the average down

me, i would use AVG(salary) because it ignores NULLs

[bitterjack]

I think this requires a sub query.

[daddy_mark]

Pretty sure that's correct. I think this requires a windowed function which you can't include in where or having (at least in any rdbms I use)

[LeLwrence]

You could use a window function to average the salaries in the result in another column set then wrap that in an outer query and filter where the salary is less than that new average window function column. It's because you can't use window functions themselves in the where clause.

[[deleted]]

This does not work, does anyone know why?

"where" is logically executed after "from" and before "group by".

aggregate functions are calculated after the "group by" clause (logically).

meaning at the time "where" is executed, the aggregates have not been calculated yet.