Combining Multiple Queries? - 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 2008 Forums

Transact-SQL (2008)

Combining Multiple Queries?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-03-27 : 12:40:17

I have the following two queries which run separately. Is is possible to combine them in to a single query to save time?

SELECT COUNT(f_ID) AS f_totalactive 
FROM tb_assets 
WHERE f_assetname IS NOT NULL 

SELECT COUNT(f_ID) AS f_totalinactive
FROM tb_assets
WHERE f_assetname IS NULL

As you can see, one query gets the results where f_assetname is NULL (to get inactive) and the other where f_assetname is NOT NULL (to get the active assets). Because the "WHERE" clause is different between the two, I didn't know if it was possible to combine them.

Thanks in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 13:02:19

SELECT SUM(CASE WHEN f_assetname IS NOT NULL THEN 1 ELSE 0 END) AS f_totalactive
SUM(CASE WHEN f_assetname IS NULL THEN 1 ELSE 0 END) AS f_totalinactive
FROM tb_assets

??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-03-27 : 13:21:31

Brett, thanks for the reply. I'm receiving an error on the "SUM"

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SUM'.

SELECT SUM(CASE WHEN f_assetname IS NOT NULL THEN 1 ELSE 0 END) AS f_totalactive
SUM(CASE WHEN f_assetname IS NULL THEN 1 ELSE 0 END) AS f_totalinactive
FROM tb_assets

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-03-27 : 13:26:06

Sorry, I should have looked closer. I had to add a comma at the end of the first line.

SELECT SUM(CASE WHEN f_assetname IS NOT NULL THEN 1 ELSE 0 END) AS f_totalactive,
SUM(CASE WHEN f_assetname IS NULL THEN 1 ELSE 0 END) AS f_totalinactive
FROM tb_assets

Thanks very much Brett, I love this forum!

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 13:40:26

"Pronoun trouble"

Sorry about the comma

D'oh

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources