CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-byunion
Just a learner
Just a learner

Reputation: 28592

How to use group by with union in T-SQL

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

Upvotes: 37

Views: 201575

Answers (5)

iTouch61
iTouch61

Reputation: 1

You do not need a subquery.
Just try out some example queries and you will see the result.

union example

select orgid from org where orgid = 123
union
select orgid from org where orgid = 123

union all example

select orgid from org where orgid = 123
union all 
select orgid from org where orgid = 123

Upvotes: 0

Songe Nsunza Jr.
Songe Nsunza Jr.

Reputation: 61

with UnionTable as  
(
    SELECT a.id, a.time FROM dbo.a
    UNION
    SELECT b.id, b.time FROM dbo.b
) SELECT id FROM UnionTable GROUP BY id

Upvotes: 4

Alex Martelli
Alex Martelli

Reputation: 881903

Identifying the column is easy:

SELECT  *
FROM    ( SELECT    id,
                    time
          FROM      dbo.a
          UNION
          SELECT    id,
                    time
          FROM      dbo.b
        )
GROUP BY id

But it doesn't solve the main problem of this query: what's to be done with the second column values upon grouping by the first? Since (peculiarly!) you're using UNION rather than UNION ALL, you won't have entirely duplicated rows between the two subtables in the union, but you may still very well have several values of time for one value of the id, and you give no hint of what you want to do - min, max, avg, sum, or what?! The SQL engine should give an error because of that (though some such as mysql just pick a random-ish value out of the several, I believe sql-server is better than that).

So, for example, change the first line to SELECT id, MAX(time) or the like!

Upvotes: 9

Thomas
Thomas

Reputation: 64645

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

Upvotes: 122

OMG Ponies
OMG Ponies

Reputation: 332631

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

Upvotes: 26

Related Questions