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.

Answer 1

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.

Answer 2

The results of the query are based on an aggregate of multiple ROWS. You take many rows with the same t.name and also their many different st.row_count values and create one row (with t.name which is the same for every aggregated row and a sum of all their t.row_count values). For such an aggregate, there's no sensible value of c.Row_ID. Each row that you group has a different value and there's no way to determine what value should be used. If the RowID is numeric, you could average or sum or perhaps take the smallest (min) or largest (max) of all the IDs. Not that it would make any sense. A value like this would simply exist for an aggregate.