T-SQL - Must have term in group by or aggregate function to select

Question

Given the following select statement: ``` Select t.name, SUM(st.row_count) as row_count From sys.dm_db_partition_stats st join sys.tables t on t.object_id = st.object_id join ClientUpdateConfig c on t.name = c.TableName Where (index_id < 2) and schema_id = schema_id('dbo') and t.type ='U' group by t.name ``` I would also like to select c.RowID as an additional field. The query works as it is but if I changed like 1 to: ``` Select t.name, SUM(st.row_count) as row_count, c.RowID as current_row ``` I get the error: Msg 8120, Level 16, State 1, Line 1 Column 'ClientUpdateConfig.RowID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Can someone explain why I'd need the select value in the aggregate function (I'm assuming SUM) or in the group by to select it? I don't understand this restriction - maybe I can adjust my query design if I can understand why this is happening.

Accepted Answer

Because it doesn't know which RowId to select.

You need either:

```
. . .
group by t.name, RowId
```

or:

```
select . . ., min(RowId) -- or max(RowId)
```

Even if you know that there is only one RowId per name, the database does not know that, so you have to explicitly say what you want to do in the query.

T-SQL - Must have term in group by or aggregate function to select

Solution

Problem