need help - 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

need help

Author

Topic

KK_Disco
Starting Member

1 Post

Posted - 2009-04-05 : 05:50:50

Is it possible to make the following statement into only 1 select statement?

select last_name
from employees
where salary < ( select avg(salary)
from employees);

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-05 : 11:23:59

No, you're never too old to Yak'n'Roll if you're too young to die.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-06 : 05:01:18

Yes it's possible - here is one way - I'm sure some-one will probably show a better one.
select last_name,salary, AvgSalary
from(
SELECT last_name,avg(salary) over (partition by 1)as AvgSalary
,salary
FROM employees
group by last_name,salary
) as Emp
where salary < AvgSalary

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:29:21

quote:
Originally posted by KK_Disco

Is it possible to make the following statement into only 1 select statement?

select last_name
from employees
where salary < ( select avg(salary)
from employees);

Why?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-09 : 09:38:18

quote:
Originally posted by darkdusky

Yes it's possible - here is one way - I'm sure some-one will probably show a better one.
select last_name,salary, AvgSalary
from(
SELECT last_name,avg(salary) over (partition by 1)as AvgSalary
,salary
FROM employees
group by last_name,salary
) as Emp
where salary < AvgSalary