How to use group by with union in T-SQL

group-by, sql, sql-server, t-sql, union

Solution

GROUP BY 1

I've never known GROUP BY to support using ordinals, only ORDER BY. Either way, only MySQL supports GROUP BY's not including all columns without aggregate functions performed on them. Ordinals aren't recommended practice either because if they're based on the order of the SELECT - if that changes, so does your ORDER BY (or GROUP BY if supported).

There's no need to run `GROUP BY` on the contents when you're using `UNION` - UNION ensures that duplicates are removed; `UNION ALL` is faster because it doesn't - and in that case you would need the GROUP BY...

Your query only needs to be:

SELECT a.id,
       a.time
  FROM dbo.TABLE_A a
UNION
SELECT b.id,
       b.time
  FROM dbo.TABLE_B b

Problem

How can I using group by with union in T-SQL? I want to group by the first column of a result of union, I wrote the following SQL but it doesn't work. I just don't know how to reference the specified column (in this case is 1) of the union result. ``` SELECT * FROM ( SELECT a.id , a.time FROM dbo.a UNION SELECT b.id , b.time FROM dbo.b ) GROUP BY 1 ```

Original source