May 2, 2011 at 12:53 pm
Hi folks, im having bother with this sql query...
List the department number and the annual salary of the department with the lowest average annual salary
select d.Department_No,MIN(AVG(Annual_Salary)) AS Lowest_Avg_Annual_Salary
from Departments d
inner join
Employees e
on d.Department_No=e.Department_No
group by d.Department_No
error im getting is:
Msg 130, Level 15, State 1, Line 89
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
May 2, 2011 at 12:55 pm
If you want the dept with the lowest , average salary you need to order by the average aggregate function and use the TOP clause
May 2, 2011 at 12:57 pm
how do i do that dave?..im just beginning sql dave
May 2, 2011 at 12:57 pm
normanshongo2003 (5/2/2011)
Hi folks, im having bother with this sql query...List the department number and the annual salary of the department with the lowest average annual salary
select d.Department_No,MIN(AVG(Annual_Salary)) AS Lowest_Avg_Annual_Salary
from Departments d
inner join
Employees e
on d.Department_No=e.Department_No
group by d.Department_No
error im getting is:
Msg 130, Level 15, State 1, Line 89
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
You can't have a minimum average. That would suggest there is more than 1 average and that just doesn't work. For example the average of 7, 8, 9 is 8. There is no minimum average.
This sounds a lot like homework so I will try to steer towards finding an answer...look at 'top' and 'order by'.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 12:58 pm
sean its just example questions for an exam that im getting next month..
May 2, 2011 at 1:00 pm
As Dave and I both suggested you should use the TOP clause and order by your aggregate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 1:01 pm
List the department number and the annual salary of the department with the lowest average annual salary
May 2, 2011 at 1:04 pm
Looks like you had a query to get the average monthly salary for each department. Now how do you find the one with the lowest?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 1:04 pm
select d.Department_No,MIN(AVG(Annual_Salary)) AS Lowest_Avg_Annual_Salary
from Departments d
inner join
Employees e
on d.Department_No=e.Department_No
order by Annual_Salary
would i just say TOP 1?..
May 2, 2011 at 1:06 pm
Well you are still nesting aggregates which will never work. Logically it just doesnt make sense. Create your query to get the average for all departments, then use TOP and order by.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 1:07 pm
lost:ermm:
May 2, 2011 at 1:10 pm
LOL. Can you get a query that gets all departments with their average annual salary? (hint: removed the MIN function). Don't worry about if it is the final answer (it isn't, it will have more records than your final desired output). But you HAVE to understand how to get here before you can filter it down.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 1:11 pm
lost:ermm:
May 2, 2011 at 1:12 pm
just tryin it here dave..lol
May 2, 2011 at 1:15 pm
select d.Department_No,(AVG(Annual_Salary)) AS Lowest_Avg_Annual_Salary
from Departments d
inner join
Employees e
on d.Department_No=e.Department_No
group by D.Department_No
Department_NoLowest_Avg_Annual_Salary
1011180.000000
2015233.333333
3013700.000000
5020746.428571
609715.789473
7018400.000000
8014400.000000
9081666.666666
11019797.500000
1908792.307692
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply