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.

Andomar · Accepted Answer · 2012-03-04 13:22:20Z

7

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.

answered Mar 4, 2012 at 13:22

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John N · Accepted Answer · 2012-03-04 13:24:29Z

1

The problem is because your GROUP BY and SELECT terms don't match up.

The simplest way to fix this is to add tbl_department.deptname into your GROUP BY, like so:

GROUP BY tblStudent_Department.deptcode, tbl_department.deptname

answered Mar 4, 2012 at 13:24

John N

1,81518 silver badges21 bronze badges

Comments

kaj · Accepted Answer · 2012-03-04 13:24:52Z

You're grouping by deptcode but selecting deptname - if you don't want to aggregate the department (which sounds like it makes sense) then you need to have the deptname in the "group by" statement:

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.deptname

Note I've removed the deptcode because I don't think you need it

If you're using aggregate functions (sum, count etc) ALL fields returned in your select statement need to either be aggregated OR in the group by clause.

Mat · Accepted Answer · 2012-03-04 13:26:01Z

0

First, Last, or put it into the group by.

The rule are:

IF you use a group by, every field is either one of the grouping fields OR one of the aggregated fields.

If you select tbl_department.deptname then you have to either group by that, too, or say WHICH ONE is taken.

Some aggretgate functions are faking that nicely - First, Last (take first or last occurance).

edited Mar 4, 2012 at 13:26

Mat

208k41 gold badges407 silver badges423 bronze badges

answered Mar 4, 2012 at 13:23

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

1 Comment

Andomar Over a year ago

Last time I checked, SQL Server didn't support the first() or last() aggregates? Isn't that Oracle / Postgres?

Collectives™ on Stack Overflow

Aggregate function in SQL Server

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related