Calculation Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculation Problem

Author

Topic

zecute
Starting Member

2 Posts

Posted - 2011-02-11 : 16:02:42

I read a course in SQL right now, and have a problem I can´t find a solution at.

The first task is the following:
View average salary for all customers in the table. I wrote the following SQL statement, which also worked:


SELECT ROUND(avg(nvl(årslön, 0))) AS Medellön FROM kund;

But it is the second task that is the problem.
The second task is the following:
Show username, fname, enamn, arslan for those customers who have an annual income of less than average salary for all customers.
It is working when I enter this SQL statement:


SELECT username, fnamn, enamn, nvl(årslön, 0) 
FROM kund 
GROUP BY årslön, username, fnamn, enamn 
HAVING nvl(årslön, 0) < 355300;

But I want to replace the 355 300 with a calculation. It is the same calculation that i used in the first task. I tried with this solution, but it does not work:


SELECT username, fnamn, enamn, nvl(årslön, 0) 
FROM kund 
GROUP BY årslön, username, fnamn, enamn 
HAVING nvl(årslön, 0) < avg(nvl(årslön, 0));

Does anyone have any suggestions for solution?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-11 : 16:32:11

NVL is not available in Microsft SQL Server, NVL is an ORACLE function. Try ask over at www.dbforums.com

select k.* from kund as k
inner join (SELECT ROUND(avg(nvl(årslön, 0))) AS Medellön FROM kund) AS i on i.medellön > k.årslön

N 56°04'39.26"
E 12°55'05.63"