How to include other grouped columns

Question

I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. This is what I've done with MySQL, now I'm porting them to MSSQL.

A simple example would be employees and projects:

select empID, fname, lname, title, dept, count(projectID)
from employees E left join projects P on E.empID = P.projLeader
group by empID

...that would work in MySQL, but MS SQL is stricter and requires that everything is either enclosed in an aggregate function or is part of the GROUP BY clause.

So, of course, in this simple example, I assume I could just include the extra columns in the group by clause. But the actual query I'm dealing with is pretty complicated, and includes a bunch of operations performed on some of the non-aggregated columns... i.e., it would get REALLY ugly to try to include all of them in the group by clause.

So is there a better way to do this?

Answer 1

You can get it to work with something around these lines:

select e.empID, fname, lname, title, dept, projectIDCount
from
(
   select empID, count(projectID) as projectIDCount
   from employees E left join projects P on E.empID = P.projLeader
   group by empID
) idList
inner join employees e on idList.empID = e.empID

This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.