How to return multiple columns when using group by in SQL

Question

I want the names of the employees along with their salary and department who have maximum salary less than 50,000. ihave the following

SELECT department, MAX(salary) as Highest salary
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000

How do I get the name of the employee to be returned?

score 11 · Accepted Answer · edited Aug 04 '11 at 10:10

11

So close...

SELECT department, name, MAX(salary) as Highest salary
FROM employees
GROUP BY department, name
HAVING MAX(salary) < 50000

After comment updates

SELECT name, department , salary
FROM employees e
   JOIN
  (
    SELECT department as dept, MAX(salary) as HighestSalary
    FROM employees
    GROUP BY department
  ) MaxE ON e.department = MaxE.dept AND e.salary = MaxE.HighestSalary

edited Aug 04 '11 at 10:10

Arihant Nahata

1,802
2
19
30

answered Aug 03 '11 at 17:29

gbn

422,506
82
585
676

3

You actually understood the question in combination with that query? Congrats! – Jacob Aug 03 '11 at 17:30
@cularis: "I want the names of the employees..". Then I guessed :-) – gbn Aug 03 '11 at 17:31
@cularis: not everyone understands you can have 2 things to group on, even if bleeding obvious to us Example: http://stackoverflow.com/q/2594829/27535 – gbn Aug 03 '11 at 17:37
what if i want the highest salary from each dept along with the name and department ? – Arihant Nahata Aug 03 '11 at 17:41
@gbn I just didn't understand the requirements. For me it sounded like he wants all employees earning less than 50000 along with their department ;) – Jacob Aug 03 '11 at 17:41
what i want now is the name, salary, dept of the employees who earn the maximum in their department ? – Arihant Nahata Aug 03 '11 at 17:44
@neverCoded: if you have 3 departments and 100 employees (say split 50-40-10) then you can have either 3 extra rows for the department or add an extra column. And now "maximum in their department": how does this match "< 50000". Think about it, then get back to us – gbn Aug 03 '11 at 17:45
it was a question asked by my teacher today ! she wanted a query for that. is it possible ? this is my second question. i got the answer for the first one. thanks :) – Arihant Nahata Aug 03 '11 at 17:47
Yes, it could be possible. But what? – gbn Aug 03 '11 at 17:49
the one i asked was my first question. the second question is, to get the name, dept, salary of the employee earning maximum in his dept.. – Arihant Nahata Aug 03 '11 at 17:51

score 5 · Answer 2 · answered Aug 03 '11 at 17:30

5

Unless I'm misunderstanding the requirement, just add employee to the select/group by?

SELECT employee, department, MAX(salary) as Highest salary
FROM employees
GROUP BY employee, department
HAVING MAX(salary) < 50000

answered Aug 03 '11 at 17:30

Derek

21,828
7
53
61

How to return multiple columns when using group by in SQL

2 Answers2