need help with SQL agregat function

[bgrzinic]

Hello guys,

 

need help regarding SQL and agregat function, here's the code:

 

SQL>

select department_id, sum(salary) place_total from employees
where department_id is not null group by department_id;

 

 

outputs:

DEPARTMENT_ID PLACE_TOTAL
------------- -----------
           90       58000
           20       19000
          110       20300
           50       17500
           80       30100
           60       19200
           10        4400

 

 

Now, how to edit the current sql statement to output department_id, and max(place_total), so the output result is:

DEPARTMENT_ID PLACE_TOTAL
------------- -----------
           90       58000

 

 

 

Anyone have an idea ?

Thanks !

[Barand]

Add

 

ORDER BY place_total DESC LIMIT 1

[bgrzinic]

No, unfortunately this doesn't do the trick, I'm in Oracle environment,

 

but this syntax also don't do the trick, which is the sam as you mentioned but in oralce syntax:

 

select * from  
(select department_id, sum(salary) place_total from employees
where department_id is not null group by department_id) tmp1 where rownum <= 1 order by place_total desc;

 

, it outputs lowest, no meter if I se ASC or DESC in order by.

[bgrzinic]

Not sure if I clearly explained. Let me try again.

 

"ORDER BY place_total DESC LIMIT 1" in Oracle should be replaced with "rownum <= 1 order by place_total desc;".

 

Al thought, I have done this, still I'm not getting correct result. It just outputs the 1st result which gets from schema by given query , which is the lowest id with the lowest salary. (tried with ASC/DESC also).

 

How do I get the ID and salary or record with the greatest salary ?

[Barand]

My apologies for assuming MySQL was being used when answering a question in the MySQL Help forum