question

Arpit.dave avatar image
Arpit.dave asked

How do I get max and min salary by department?

I have three tables. 1. employee which has column names--> (emp_id,emp_name,dep_id) 2. department which has column names-->(dep_id,dep_name) 3. salary which has column names-->(emp_id,salary) Now I want to get output with emp_id with their dep_id and their salary and also display min and maximum salary of their department in every row. ''emp_id dep_id salary min salary maxsalary''
aggregatesoracle-sql-developerobject-names
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
GPO avatar image
GPO answered
I've reformatted your question to improve readability. Not 100% sure I understand your question but this might get you off and running: SELECT emp.emp_id ,dep.dep_id ,sal.salary ,max(sal.salary) over(partition by dep.dep_id) as MaxSalary --max salary for each department ,min(sal.salary) over(partition by dep.dep_id) as MinSalary --max salary for each department FROM dbo.employee emp JOIN dbo.department dep ON emp.dep_id = dep.dep_id JOIN dbo.salary sal ON emp.emp_id = sal.emp_id Out of interest, what is the primary key of the salary table? For the benefit of others and the ongoing viability of the forum, don't forget to mark the correct answer as correct, and upvote any other useful answers.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.