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
 General SQL Server Forums
 New to SQL Server Programming
 Sum of 2 selects

Author  Topic 

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 15:42:40
please forgive this noob but Is there a way to sum up these 2 selects? 

select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867')


select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and (a.Premiumplan <> b.Premium_plan)
and a.Premiumplan in ('87545','234564'23165786','564567','23154','4897897','57897')
and b.Premium_plan not in ('87545','234564'23165786','564567','23154','4897897','57897')

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-02 : 15:44:54
Do you mean:
SELECT Revenue =
(select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867'))+
(select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and (a.Premiumplan <> b.Premium_plan)
and a.Premiumplan in ('87545','234564'23165786','564567','23154','4897897','57897')
and b.Premium_plan not in ('87545','234564'23165786','564567','23154','4897897','57897'))
Go to Top of Page

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 15:56:25
quote:
Originally posted by ms65g

Do you mean:
SELECT 
(select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867'))+
(select count(*)
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and (a.Premiumplan <> b.Premium_plan)
and a.Premiumplan in ('87545','234564'23165786','564567','23154','4897897','57897')
and b.Premium_plan not in ('87545','234564'23165786','564567','23154','4897897','57897'))




Yes that works, thank you so much. But how do I name the column? I want it to be called Revenue, where do I put that?
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-02 : 15:58:20
Try this too and see my edited first post

select count(*) AS Revenue
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and (
(
a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867')
) OR
(
and (a.Premiumplan <> b.Premium_plan)
and a.Premiumplan in ('87545','234564','23165786','564567','23154','4897897','57897')
and b.Premium_plan not in ('87545','234564','23165786','564567','23154','4897897','57897')
)
)
Go to Top of Page

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 16:13:53
quote:
Originally posted by ms65g

Try this too and see my edited first post

select count(*) AS Revenue
from history a, Primary.dbo.Records b
where a.RecordID = b.RecordID 
and a.maildate = '20100224' 
and a.State in ('Fulfilled', 'Order')
and a.Premiumplan <> ''
and a.Code in ('102', '103', '104')
and (
(
a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867')
) OR
(
and (a.Premiumplan <> b.Premium_plan)
and a.Premiumplan in ('87545','234564','23165786','564567','23154','4897897','57897')
and b.Premium_plan not in ('87545','234564','23165786','564567','23154','4897897','57897')
)
)





I get an error here: and
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-02 : 16:15:11
-> I get an error here: and <-
Remove it
Go to Top of Page

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 16:19:05
quote:
Originally posted by ms65g

-> I get an error here: and <-
Remove it



It would just return the result of the second select and not the first when I remove the and
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-03-02 : 16:36:16
quote:
Originally posted by fengfeng

quote:
Originally posted by ms65g

-> I get an error here: and <-
Remove it



It would just return the result of the second select and not the first when I remove the and
Why do you think that? Did you test it?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-03-02 : 16:38:37
You can also use UNION if the OR clause is causing performance issues:
SELECT SUM(Revenue) AS Revenue
FROM
(
	select count(*) AS Revenue
	from history a, Primary.dbo.Records b
	where a.RecordID = b.RecordID 
	and a.maildate = '20100224' 
	and a.State in ('Fulfilled', 'Order')
	and a.Premiumplan <> ''
	and a.Code in ('102', '103', '104')
	and a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
	and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867')

	UNION ALL

	select count(*)
	from history a, Primary.dbo.Records b
	where a.RecordID = b.RecordID 
	and a.maildate = '20100224' 
	and a.State in ('Fulfilled', 'Order')
	and a.Premiumplan <> ''
	and a.Code in ('102', '103', '104')
	and (a.Premiumplan <> b.Premium_plan)
	and a.Premiumplan in ('87545','234564', '23165786','564567','23154','4897897','57897')
	and b.Premium_plan not in ('87545','234564', '23165786','564567','23154','4897897','57897')
) AS T
Go to Top of Page

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 16:48:25
quote:
Originally posted by Lamprey

You can also use UNION if the OR clause is causing performance issues:
SELECT SUM(Revenue) AS Revenue
FROM
(
	select count(*) AS Revenue
	from history a, Primary.dbo.Records b
	where a.RecordID = b.RecordID 
	and a.maildate = '20100224' 
	and a.State in ('Fulfilled', 'Order')
	and a.Premiumplan <> ''
	and a.Code in ('102', '103', '104')
	and a.Premiumplan in ('256456', '324564','23154','315645','21244564','1234864','564867')
	and b.Premium_plan not in ('256456', '324564','23154','315645','21244564','1234864','564867')

	UNION ALL

	select count(*)
	from history a, Primary.dbo.Records b
	where a.RecordID = b.RecordID 
	and a.maildate = '20100224' 
	and a.State in ('Fulfilled', 'Order')
	and a.Premiumplan <> ''
	and a.Code in ('102', '103', '104')
	and (a.Premiumplan <> b.Premium_plan)
	and a.Premiumplan in ('87545','234564', '23165786','564567','23154','4897897','57897')
	and b.Premium_plan not in ('87545','234564', '23165786','564567','23154','4897897','57897')
) AS T



I'm not getting the right results. I did test it out but wont be the same results as if I was to run those 2 selects separately and then adding them up. Thank you for all your help though. Your first solution worked but I don't know how to put a name on it
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-02 : 16:54:26
Have you seen the edit (in red) in ms65g's first post?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

fengfeng
Yak Posting Veteran

64 Posts
Posted - 2010-03-02 : 16:55:11
quote:
Originally posted by webfred

Have you seen the edit (in red) in ms65g's first post?

No, you're never too old to Yak'n'Roll if you're too young to die.


Ahh now i see it, thank you!! It works perfectly now didnt even know you can do that
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-02 : 16:56:35
fine

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site