Combining and adding multiple selects

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

Combining and adding multiple selects

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-02-26 : 15:30:36

I have the following 4 select statements. How can I combine everything into 1 sql statement? There are 2 selects for Total_feather_1 and 2 selects for total_feather_2, how can I combine everything into 1 statement where the 2 total_feather_1 states are counted together and the 2 total_feather_2 selects are also counted together, so essentially I want to have just 2 columns instead of 4?


Select Count (*) as total_Feather_1
from history
where recorddate='20091230'
and package in ('Blanket', 'Feather')
--and calldate >= '20100201'
and Code like '%100005%'
AND Offertype IN ('101', '102', '103')

Select Count (*) as total_Feather_1
from history
where recorddate='20091230'
and package in ('Blanket', 'Feather')
--and calldate >= '20100201'
and Code like '%100006%'
AND Offertype IN ('101', '102', '103')

Select Count (*) as total_Feather_2
from history
where recorddate='20091230'
and package in ('Blanket', 'Feather')
--and calldate >= '20100201'
and Code like '%100007%'
AND Offertype IN ('101', '102', '103')

Select Count (*) as total_Feather_2
from history
where recorddate='20091230'
and package in ('Blanket', 'Feather')
--and calldate >= '20100201'
and Code like '%100008%'
AND Offertype IN ('101', '102', '103')

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-02-26 : 19:41:07

Does it work with "union all"?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-26 : 22:13:32

[code]
SELECT COUNT (CASE WHEN Code LIKE '%100005%'
OR Code LIKE '%100006%'
THEN Code
END) AS total_Feather_1,
COUNT (CASE WHEN Code LIKE '%100007%'
OR Code LIKE '%100008%'
THEN Code
END) AS total_Feather_2,
FROM history
WHERE recorddate = '20091230'
AND package IN ('Blanket', 'Feather')
AND Offertype IN ('101', '102', '103')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 01:56:32

[code]SELECT COUNT (CASE WHEN Code LIKE '%100005%' THEN Code END) AS total_Feather_1,
COUNT (CASE WHEN Code LIKE '%100007%' THEN Code END) AS total_Feather_2
FROM history
WHERE recorddate = '20091230'
AND package IN ('Blanket', 'Feather')
AND Offertype IN ('101', '102', '103')
UNION ALL
SELECT COUNT (CASE WHEN Code LIKE '%100006%' THEN Code END)
COUNT (CASE WHEN Code LIKE '%100008%' THEN Code END)
FROM history
WHERE recorddate = '20091230'
AND package IN ('Blanket', 'Feather')
AND Offertype IN ('101', '102', '103');

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources