SQL group by problems HELP

ok in my oracle class we have a problem that was given to us. He wants to the max average salary, grouped by departments AND the department number of the department it is in. Ive been getting errors all day and no one in my class has figured it out yet. anyway here is what i have.

select max(avg(salary)), department_id
from employees
group by department_id;

ive been getting errors about not being a single group group function.

i dont know how to do this one at all. Can anyone shed some light on how to get the max avg salary grouped by department #, AND the department number.

thanks

max() will return just one result, the maximum. If I've read the problem correctly you'd want

select avg(salary), department_id
from employees
group by department_id
order by salary asc;

This should return one result for each department_id, ordered by their average salaries.

Edit: Nevermind, ignore that. I think my query will return the average over the entire table... anyways, this should really be in the Oracle forum, not the MySQL forum.

It will return the average by department, catfish, not the overall average. =)

So I was right? Um... wow... *blinks*

yeah, the query in post #2 gives the average salary per department, but that's not what the original question was

the original question was to find the max average department salary, and say which department it's in

i know how to do it in every other database except oracle, which i flat out refuse to install (a 650meg download? you gots to be kidding me!)



besides, we don't do homework questions on these forums, do we

Just to make sure, the question was "find the max average department salary"? so you would just be returning 1 row , right?

If that's the case, break it down for yourself and pose the question, how do you get the average dept salaries listed?


select avg(salary), department_id, dept_name
from employees
group by department_id, dept_name;

(we are grouping by dept_name because it is one of the fields that you want to return.)

after that, you'd want to find the max of the results of that query(or rather, subquery) ^^.

Post your solution when you get it

its not a homework question, if it was a homework question id have the answer in my book. Im looking for the max average salary and its department number, so yes it would only return one row. I can get it to pull the avg sal and department id no problem, but if u try and nest the group by functions max(avg(salary)) then u get the not a single group group function. Its not a homework question or a test question, just something the students couldnt figure out so the teacher makes us thnk about it and try and find the answer. Anyone know how to do this?? please help if you know how to do this. ive been racking my brain all day over this question. Thanks

Do i need to do something to the department_id in the select statement (what i might need to do to it i dont know). Or do i need a having clause in there? the having clause wont let you nest group by functions so im at a loss completely... please someone help...

the answer is "secretly" in my post

it's not all that secret, either, wordracr -- you actually come out and say it

Too bad, it's great and who doesn't have a fast internet connection nowadays?
And btw ... I could ask you questions about analytic functions ...

like this?

rjett3345,

The beginner way - break the problem into two easy questions:
1) What is the maximum of the average salaries for all departments?
2) Given the maximum average salary from #1, which department(s) has an average salary that is the same as #1?


- OR -

The intermediate way - follow wordarcs advice:
1) What are the average salaries for all departments?
2) Given that, can I somehow "pluck" the top element (think: ORDER BY and ROWNUM)

- OR -

The advanced way - impress the hell out of your professor, google for "analytic functions", spend an afternoon learning how the pros do it.

- OR -

Just keep posting your question to different discussion forums. Someone will answer it sooner or later.

select *
from (select department_id, avg(salary) salary
from employees
group by department_id
order by avg(salary) desc)
where rownum = 1;