2

I'm stuck to this error:

Msg 8120, Level 16, State 1, Line 2 Column 'Subjects.off_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't know what is wrong with this query.

use Enlistment
go

SELECT Subjects.off_CODE, Subjects.subj_CODE, Subjects.description, 
Subjects.unit, COUNT(Enlistment.off_CODE) FROM Enlistment, Subjects
WHERE Subjects.off_CODE = 11315
GROUP BY Enlistment.off_CODE
dugas
  • 12,025
  • 3
  • 45
  • 51
eaponz
  • 574
  • 1
  • 16
  • 32
  • 1
    I think so you are using MSSQL.. You need to use all the columns other than the aggregate one in the group by clause.. So your group by clause will be `GROUP BY Subjects.off_CODE, Subjects.subj_CODE, Subjects.description, Subjects.unit` – Meherzad Apr 11 '13 at 14:46
  • 3
    Are you aware that you are performing a cartesian product between your tables? – Lamak Apr 11 '13 at 14:48
  • Please consider using ansi syntax: http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax – Nick Vaccaro Apr 11 '13 at 15:03

3 Answers3

1

If the column is selected, it must appear in the GROUP BY clause, unless it is contained in an aggregate function (just like the error message says).

use Enlistment
go

SELECT Subjects.off_CODE, Subjects.subj_CODE, Subjects.description, 
Subjects.unit, COUNT(Enlistment.off_CODE) FROM Enlistment, Subjects
WHERE Subjects.off_CODE = 11315
GROUP BY Subjects.off_CODE, Subjects.subj_CODE, Subjects.description, 
Subjects.unit

So, in your example, the only field that is selected that does not have to be included in the GROUP BY clause is Enlistment.off_CODE because it is used in the aggregate function COUNT (COUNT(Enlistment.off_CODE)). All of the other fields must be included in the GROUP BY clause.

dugas
  • 12,025
  • 3
  • 45
  • 51
0

Did you try this:

GROUP BY Enlistment.off_CODE, Subjects.off_CODE

or

SELECT DISTINCT Subjects.off_CODE, Subjects.subj_CODE, Subjects.description, 
Subjects.unit, COUNT(Enlistment.off_CODE) FROM Enlistment, Subjects
WHERE Subjects.off_CODE = 11315
GROUP BY Enlistment.off_CODE, Subjects.off_CODE

?

Question3CPO
  • 1,202
  • 4
  • 15
  • 29
0

You are telling the query to GROUP BY a column that you are not selecting. You will need to make sure that you SELECT the columns that are contained within the GROUP BY field.

@Question3CPO's would work except that it includes Subjects.subj_CODE in the SELECT statement and that is probably not contained within the function.

jcsbrotha
  • 89
  • 3
  • 15