SOLVED: Simple average query SQL

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

SOLVED: Simple average query SQL

Author

Topic

aprilrocks92
Starting Member

3 Posts

Posted - 2012-12-03 : 17:20:16

I have the following relational database:

employee(employee_id (primary key), salary)
manages(manager_name, employee_id (primary key))
guest(guest_id (primary key), loyalty_number, guest_name)
stays(room_id (primary key), guest_id, start_date, end_date)

I also have the following query, "Find the names of all the managers whose salary are $1000 more than the average employee salary").

The query I have written is:

select manager_name
from manages, employee
where salary > (select avg(salary) + 1000
from employee
where employee.employee_id = manages.employee_id)

However, this does not seem to give me the right answer. Any suggestions?

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-03 : 17:32:59

SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

aprilrocks92
Starting Member

3 Posts

Posted - 2012-12-03 : 17:36:27

Thank you. However, it does not seem to work. It generates the following message:
"column 'salary' does not exist".

quote:
Originally posted by shilpash

SELECT manager_name
FROM manages
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-03 : 17:36:59

try now.I have edited the code

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-03 : 17:38:19

Use this--

SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

aprilrocks92
Starting Member

3 Posts

Posted - 2012-12-03 : 17:45:37

Thank you very much, this works!

quote:
Originally posted by shilpash

Use this--

SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-03 : 17:48:58

You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources