Menu
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 
428
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... There are use cases for wanting to use GROUP BY on the results of a union.
We can use GROUP BY to group together rows that have the same value in the Animal column, while using COUNT() to find out how many ID's we have in each group. It returns a table with three rows (one for each distinct animal).
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. But they need not have to be in the same length.
You need to alias the subquery. Thus, your statement should be:
Select Z.id From ( Select id, time From dbo.tablea Union All Select id, time From dbo.tableb ) As Z Group By Z.id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
