Aggregate function in SQL Server

Question

I'm getting really frustrated about SQL Server. I'm just trying to join 3 tables, very simple and easily done in mysql. But in SQL Server it keeps telling me to contain tbl_department.deptname in an aggregate function. But what aggregate function could I possibly use in a simple string?

SELECT      
    COUNT(tblStudent_Department.student_id) AS Expr2, 
    tbl_department.deptname AS Expr1
FROM          
    tblStudent_Department 
LEFT OUTER JOIN
    tbl_department ON tblStudent_Department.deptcode = tbl_department.deptcode 
LEFT OUTER JOIN
    tblStudent ON tblStudent_Department.student_id = tblStudent.studentid
GROUP BY 
    tblStudent_Department.deptcode

Please help.

Answer 1

The database doesn't know that if you group on deptcode, you're implicitly grouping on deptname. You must tell SQL Server this by adding the column to the group by:

GROUP BY tblStudent_Department.deptcode, tbl_department.deptname

MySQL is special in that it basically picks a random row if you don't specify an aggregate. This can be misleading and lead to wrong results. As in many other things, MySQL has the more pragmatic solution, and SQL Server the more correct one.