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
