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''
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.
18 People are following this question.
