CODEWITHSUNDEEP
sqloracle-databaseaverage
Mongzyy
Mongzyy

Reputation: 123

Find value smaller than average in Oracle SQL

I have this code:

SELECT username, first_name, last_name, NVL(salary,0) "salary"
FROM customer
WHERE NVL(salary,0) < AVG(NVL(salary, 0));

Trying to find out which users have lower salary than average and one user has no salary ("null" which I must convert to 0).

The last statement "avg(nvl(salary,0))" doesn't work and I can't for the life of me figure out why. If I replace the statement with the actual number of the average everything works just fine.

Upvotes: 1

Views: 1715

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

Here's a method using an analytic function:

select username,
       first_name,
       last_name,
       salary
from   (select username,
               first_name,
               last_name,
               nvl(salary,0) salary,
               avg(nvl(salary,0)) over () avg_all_salaries
        from   customer)
where  salary < avg_all_salaries

It makes it easy to also display the average of all salaries, should that ever arise.

Upvotes: 4

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

SELECT username,first_name,last_name, NVL(salary,0) "salary"
FROM customer
WHERE NVL(salary,0) < (SELECT AVG(NVL(salary,0)) FROM customer);

This pretty easy to get.

Upvotes: 0

smnbbrv
smnbbrv

Reputation: 24551

You can try to pre-select the average:

select username,first_name,last_name,nvl(salary,0) "salary"
from customer
where nvl(salary,0) < (select avg(nvl(salary,0)) from customer);

You can use avg only in select and having clauses, that is why you have an error.

Also don't use nvl but use coalesce instead, this should be way faster on big data.

Upvotes: 2

Related Questions