group by two joined columns

CREATE TABLE T1 (a int);

CREATE TABLE T2 (a int);

SELECT T1.a , T2.a
FROM T1
JOIN T2 ON T1.a=T2.a
GROUP BY T1.a;

I get an error from this code:

Msg 8120 Level 16.
Column ‘T2.a’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I would have expected this to work because T1.a and T2.a are joined

Answer

I would have expected this to work because T1.a and T2.a are joined

This makes sense, but unfortunately: no. SQL Server sees two different unaggregated columns in the SELECT clause, and wants them in the GROUP BY clause too:

SELECT T1.a, T2.a
FROM T1
JOIN T2 ON T1.a = T2.a
GROUP BY T1.a, T2.a

On the other hand, since you are joining on these columns, you also know that both have the same value, so why would you need to output both in the resultset?

SELECT T1.a
FROM T1
JOIN T2 ON T1.a = T2.a
GROUP BY T1.a

Some databases support the USING syntax on joins, which disambiguate the column names when both are the same – but not SQL Server:

SELECT a
FROM T1
JOIN T2 ON USING(a)
GROUP BY a