Combining of statements - 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

SQL Server Development (2000)

Combining of statements

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2006-05-18 : 10:00:19

I having a bit of a bad day today and have forgotten how best to handle this problem. I know I can handle it in a temp table but dont want to.

The scenario is that I have the following 2 sql statements

select order_id,person_id, sum(pages) from tbldocuments where [primary] <> 2 and folder = '10' group by order_id, person_id
select order_id,person_id, sum(pages)+2 from tbldocuments where folder = '10' group by order_id, person_id

Which produces the output

17612 161520651 18
17631 235868283 17

and

17612 161520651 26
17631 235868283 24

but I prefer to have it as

17612 161520651 18 26
17631 235868283 17 24

Can you please put me out of my misery please

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 10:03:37

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2006-05-18 : 10:09:22

I don't want to return in a single field I want its own field which perhaps wasn't clear in my example.

Sorry but is there another approach?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 10:34:25

What happens when you have 100 different values in sum(pages)? Do you want to have 100 columns?
If you want to generate cross-tab reports look for cross-tab reports in sql server help file and also this link http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2006-05-18 : 10:40:16

There will only ever be two columns of sums and a total of 4 columns in all

jparker
Posting Yak Master

118 Posts

Posted - 2006-05-18 : 10:50:16

This is basically what I want to do but don't believe it is the best way to do it

create table #temptable (order_id varchar(30), person_id varchar(30), original int)

insert into #temptable select order_id,person_id, sum(pages) from tbldocuments where [primary] <> 2 and folder = '10' group by order_id, person_id

select t.*, sum(d.pages)+2 from #temptable t
join tbldocuments d on d.order_id = t.order_id
where d.folder = '10' group by d.order_id, d.person_id, t.order_id, t.person_id, t.original

drop table #temptable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 10:51:19

Try this


Select order_id,person_id,
	Sum(case when [primary] <> 2 then pages else 0 end) as Sum1,
	Sum(pages)+2 as Sum2,
from tbldocuments where folder = '10' group by order_id, person_id

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2006-05-18 : 10:58:08

As always brilliant madhivanan. I knew there was a simplier way than my messy code.

Cheers mate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 11:04:01

Thats why I asked you to look for cross-tab reports in sql server help file