Group by with Union

There is always a question for fresh SQL Engineers, how do I add group by with Union statement, doing this throw error.

It is quite possible and very easy to implement. Lets take an example to check this

Here we are creating two table TableA and TableB with two fileld id and name, then we will do the union with group by

create table TableA

(id int,

name varchar(15))

Command(s) completed successfully.

create table TableB

(id int,

name varchar(15))

Command(s) completed successfully.

Inserting few rows into TableA

insert into TableA values(1,'Prachi')

insert into TableA values(2,'Shilpa')

insert into TableA values(3,'Bhagyashree')

  

Inserting few rows into TableB

insert into TableB values(1,'Pragyan')

insert into TableB values(3,'Subankeri')

insert into TableB values(4,'Neha')

Now I want to do union of these two tables and group the result by ID

select id,name from TableA

union all

select id,name from TableB

group by id

Msg 8120, Level 16, State 1, Line 3

Column 'TableB.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Let see what an alternative is

select * from

(select id,name from TableA

union all

select id,name from TableB)as FinalTable(id,name)

group by id,name

Conclusion:  we can use group by after putting the union query result in a select statement thereafter doing group by.

Programmer born alone as a warrior, live alone as a fighter  and die alone as a calm, sometime in the course of his programming life he create a false impression for the  moment that he is not unaided, he too has girl friend to love beyond code.  

Post Reference: Vikram Aristocratic Elfin Share