How to use GROUP BY when fetching values from More than one Table

Question

We have 2 Tables Employees and Department.

We want to show the maximum salary from each department and their corresponding employee name from the employee table and the department name from the department table.

Employee Table

EmpId | EmpName |salary |DeptId

  101   shubh1    1000    1
  101   shubh2    4000    1
  102   shubh3    3000    2
  102   shubh4    5000    2
  103   shubh5    12000   3
  103   shubh6    1000    3
  104   shubh7    1400    4
  104   shubh8    1000    4

Department Table

DeptId | DeptName
  1      ComputerScience
  2      Mechanical
  3      Aeronautics
  4      Civil

I tried doing it but was getting error

SELECT DeptName FROM Department where deptid IN(select MAX(salary),empname,deptid
FROM Employee
GROUP By Employee.deptid)

Error

Token error: 'Column 'Employee.EmpName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' on server 4e0652f832fd executing on line 1 (code: 8120, state: 1, class: 16)

Can someone please help me.

And please show some sample data and desired results - not just your table structure. — Dale K, Oct 19 '22 at 20:44
As stated.. please do not "sh0tgun" tag your questions. Please tag it the the correct/germane tags. In this case, a single RDBMS... — granadaCoder, Oct 19 '22 at 20:45
`.. where deptid IN(select MAX(salary),empname ..` o_O where one value in two-value pack? really? — Akina, Oct 19 '22 at 20:49
Removed conflicting tags... feel free to add the correct **one** back. — Dale K, Oct 19 '22 at 20:53
@KeithL See error message.. this is SQL Server with guarantee. — Akina, Oct 19 '22 at 21:13
Also note how do you want to handle duplicates in one department with same max salary? Show 1 (how do we pick), show all? — xQbert, Oct 20 '22 at 12:30

xQbert · Answer 1 · 2022-10-21T18:18:26.030

Now that I know it's MS SQL Server, technically; we could use cross or outer Apply; it's a table value function not a join per say... but this will depend on the version of SQL Server; and if you want data if it doesn't exist in another

I find this the "Best" Design pattern to use for this type of query.

What the engine does is for each record in department, it runs a query for the employees Finding those in that department returning the 1 record having the max salary. With top we could specify with ties to return more than one. but we would need to know how to handle Ties of salary. Use top 1 with ties or order the results so you get the "Top" result you want.

Demo: dbfddle.uk

SELECT Sub.empName, Sub.Salary, D.DeptName
FROM Department D
CROSS Apply (SELECT Top 1 * 
          --(SELECT TOP 1 with Ties * -- could use this if we ties
             FROM  Employee E
             WHERE E.DeptID = D.DeptID
             ORDER BY Salary Desc) Sub  --add additional order by if we don't want ties.

The cross apply gives us:

+---------+--------+-----------------+
| empName | Salary |    DeptName     |
+---------+--------+-----------------+
| shubh2  |   4000 | ComputerScience |
| shubh4  |   5000 | Mechanical      |
| shubh5  |  12000 | Aeronautics     |
| shubh7  |   1400 | Civil           |
+---------+--------+-----------------+

Before window functions, before cross Apply or lateral... We'd write an inline view It would get us the max salary for each dept, we then join that back to our base tables to find the employee within each dept with max salary...

Demo: DbFiddle.uk

SELECT E.*, D.*
FROM Employee E
INNER JOIN Department D
 on E.DeptID = D.DeptID
INNER JOIN (SELECT MAX(SALARY) maxSal , DeptID 
            FROM Employee 
            GROUP BY DeptID) Sub
on Sub.DeptID = E.DeptID
and Sub.MaxSal = E.Salary

One has to do a join to get the department info an the employee info. However, we can eliminate the join for salarymax by using exists and correlation instead.

Demo DbFiddle.uk

SELECT E.*, D.*
FROM Employee E
INNER JOIN Department D
 on E.DeptID = D.DeptID
WHERE EXISTS (SELECT MAX(Sub.SALARY) maxSal , Sub.DeptID 
            FROM Employee  Sub
            WHERE sub.DeptID=E.DeptID  --correlation 1
            GROUP BY Sub.DeptID
            HAVING E.Salary = max(Sub.Salary)) --correlation 2

We could eliminate the last join too I suppose:

Demo: Dbfiddle.uk

SELECT E.*, (SELECT DeptName from Department where E.DeptID = DeptID)
FROM Employee E
WHERE EXISTS (SELECT MAX(Sub.SALARY) maxSal , Sub.DeptID 
            FROM Employee  Sub
            WHERE sub.DeptID=E.DeptID  --correlation 1
            GROUP BY Sub.DeptID
            HAVING E.Salary = max(Sub.Salary)) --correlation 2

The top 3 give us this result:

+-----+---------+--------+--------+--------+-----------------+
| id  | empName | salary | deptID | DeptID |    DeptName     |
+-----+---------+--------+--------+--------+-----------------+
| 101 | shubh2  |   4000 |      1 |      1 | ComputerScience |
| 102 | shubh4  |   5000 |      2 |      2 | Mechanical      |
| 103 | shubh5  |  12000 |      3 |      3 | Aeronautics     |
| 104 | shubh7  |   1400 |      4 |      4 | Civil           |
+-----+---------+--------+--------+--------+-----------------+

@ShubhamVivek - with the given schema and the requirement to show both the employee and department name, no one table holds all of the information. So a join is necessary. — Ben Thul, Oct 19 '22 at 23:13
@ShubhamVivek Yes we could do this without a join I suppose; but it's not the best design pattern; unless we use the cross apply table value function in "newer" SQL server versions. — xQbert, Oct 20 '22 at 12:47

score 0 · Answer 2 · answered Oct 19 '22 at 22:16

select  salary
       ,EmpName 
       ,DeptName
from    (
        select   e.salary
                ,e.EmpName
                ,d.DeptName
                ,rank() over(partition by e.DeptId order by e.salary desc) as rnk
        from    Employee e join Department d on d.DeptId = e.DeptId
        ) t
where   rnk = 1

salary	EmpName	DeptName
4000	shubh2	ComputerScience
5000	shubh4	Mechanical
12000	shubh5	Aeronautics
1400	shubh7	Civil

Fiddle

How to use GROUP BY when fetching values from More than one Table

2 Answers2