Select sum and union datasets, this should be ez

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)

Select sum and union datasets, this should be ez

Author

Topic

leifthoreson
Starting Member

16 Posts

Posted - 2005-10-25 : 15:08:51

Hi all, and thank you in advance.
I have 2 views that each return 3 columns

view1
-------------
Description , repairs , code

view2
----------------
Description , repairs, code

Description is char(10)
repairs is int
code is char(5)

data looks like this

view1
-----------
Test1failed ,10, F28/F10
Test2failed ,5, F23/F10
Test1failed ,10, F28/F10
Test3failed ,15, F24/F10

view2
-----------
Test1failed ,10, F28/F10
Test2failed ,9, F23/F10
Test1failed ,11, F28/F10
Test3failed ,5, F24/F10

the base quiry I started with is
select Distinct [Description], sum(repairs) , code from view1
group by [Description],code
which returns
Test1failed ,20, F28/F10
Test2failed ,5, F23/F10
Test3failed ,15, F24/F10

and the base quiry for view2 I started with is
select Distinct [Description], sum(repairs) , code from view2
group by [Description],code
which returns
Test1failed ,21, F28/F10
Test2failed ,9, F23/F10
Test3failed ,5, F24/F10

I need to cobine the data returned into one sum

something like
select Distinct [Description], sum(repairs) , code from (
select [Description], repairs , code from view1
union all
select [Description], repairs , code from view2)

of course this doesn't work

data should look like
Test1failed ,41, F28/F10
Test2failed ,14, F23/F10
Test3failed ,20, F24/F10

I've been trying for two days to figure out how to select from the returned dataset... Please help!
Leif

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-25 : 15:44:54

You seemed to be pretty close... biggest thing you needed was a Group By clause


Create Table #View1
(
	[Description] varchar(100),
	Repairs int,
	Code varchar(100)
)
Insert Into #View1
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test2failed', 5, 'F23/F10' Union All
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test3failed', 15, 'F24/F10'

Create Table #View2
(
	[Description] varchar(100),
	Repairs int,
	Code varchar(100)
)
Insert Into #View2
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test2failed', 9, 'F23/F10' Union All
Select 'Test1failed', 11, 'F28/F10' Union All
Select 'Test3failed', 5, 'F24/F10'


Select
	[Description], 
	Repairs = sum(repairs), 
	code 
from 
	(
	select [Description], repairs , code from #view1
	union all
	select [Description], repairs , code from #view2
	) A
Group By [Description], Code
Order By [Description]

Drop Table #View1
Drop Table #View2

/*
Test1failed ,41, F28/F10
Test2failed ,14, F23/F10
Test3failed ,20, F24/F10
*/

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

leifthoreson
Starting Member

16 Posts

Posted - 2005-10-25 : 17:33:07

quote:

Originally posted by Seventhnight

You seemed to be pretty close... biggest thing you needed was a Group By clause


Create Table #View1
(
	[Description] varchar(100),
	Repairs int,
	Code varchar(100)
)
Insert Into #View1
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test2failed', 5, 'F23/F10' Union All
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test3failed', 15, 'F24/F10'

Create Table #View2
(
	[Description] varchar(100),
	Repairs int,
	Code varchar(100)
)
Insert Into #View2
Select 'Test1failed', 10, 'F28/F10' Union All
Select 'Test2failed', 9, 'F23/F10' Union All
Select 'Test1failed', 11, 'F28/F10' Union All
Select 'Test3failed', 5, 'F24/F10'


Select
	[Description], 
	Repairs = sum(repairs), 
	code 
from 
	(
	select [Description], repairs , code from #view1
	union all
	select [Description], repairs , code from #view2
	) A
Group By [Description], Code
Order By [Description]

Drop Table #View1
Drop Table #View2

/*
Test1failed ,41, F28/F10
Test2failed ,14, F23/F10
Test3failed ,20, F24/F10
*/

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Thanks

wow... I was just missing a little bit....
Your sqlest

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources