Multiple select with group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple select with group by

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-21 : 08:01:20

Harsh writes "Hi there,

I was wondering if someone would know how to do a union of 2 tables with similar data (and columns).. and then do a group by on them to them to get the summation of records.

for example:

I have 2 tables, both with lists of various products, that are replicated throughout each table.. each with different quantity values. I am looking to union the 2 tables, then group them to get the quantity sums of the products grouped.

Any help would be greatly appreciated, thanks!

-Harsh"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-21 : 08:42:34

[code]
create table productsA( productid int,quantity int)
insert into productsA
select 0,2
union select 0,1
union select 0,3
union select 1,1
union select 1,2
union select 2,2

create table productsB( productid int,quantity int)
insert into productsB
select 0,2
union select 0,1
union select 0,3
union select 1,1
union select 1,2

select distinct productid,sum(quantity) ttlquantity
from
(
select productid,quantity from productsA
union all
select productid,quantity from productsB
)
a
group by productid

drop table productsA
drop table productsB
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources