invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

use qcvalues_test
go

select [finalConc]
      ,[rowid] from qvalues where rowid in (select rowid from batchinfo where instrument = 'TF1') 
and name='qc1'
and compound='etg'
group by finalConc
having COUNT(rowid)=2

why am i getting this error

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

possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](http://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Tanner, Apr 29 '15 at 13:44

score 4 · Accepted Answer · answered Dec 08 '10 at 22:35

4

The error message is self-explanatory - you need to use an aggregate function:

SELECT
    [finalConc],
    MIN([rowid]) AS minRowId,
    MAX([rowid]) AS maxRowId
FROM ...

answered Dec 08 '10 at 22:35

Mark Byers

811,555
193
1,581
1,452

+1 Given the OP's `HAVING` clause this is the only sensible answer. – Martin Smith Dec 08 '10 at 23:30

score 0 · Answer 2 · answered Dec 09 '10 at 10:12

0

Hi herrow To solve this problem you need to replace select [finalConc] ,[rowid] from qvalues with select [finalConc] ,Count([rowid]) from qvalues

answered Dec 09 '10 at 10:12

invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

2 Answers2