Multiple Counts with different Where Clause

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 2005 Forums

Transact-SQL (2005)

Multiple Counts with different Where Clause

Author

Topic

batcater98
Starting Member

22 Posts

Posted - 2008-01-04 : 16:26:13

Looking to in one SQL statement count based on two different WHERE Clauses - I would also like to be able to use @parameters in the statement as I am running the query in SSIS report server.

Any ideas.

Thanks,
BC98

Regards,
The Dark Knight
-Give What is Right, Not What is Left-

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-04 : 16:40:25

YOu can do

Select SUM(Case WHEN ---your where condition THEN 1 else 0 end) as COunt1, Sum(Case WHEN ----your 2nd where condition THEN 1 else 0 end) as Count2
FROM [Table]

You can use parameters in the select statement...I have yet to migrate my box to 2005, so there may be other ways to do it in SSIS depending on the complexity of the query etc..

Poor planning on your part does not constitute an emergency on my part.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-06 : 21:14:00

select
case when @parameter=1 then
(select count(*) from BLAH1 where CLAUSE 1)
when @parameter=2 then
(select count(*) from BLAH2 where CLAUSE 2)
end

Might be quicker because each statement will be parsed in its own right and will make full use of all indexes. I think the case method will always scan the whole table (which may or may not be an issue depending on size). It also gives you the chance to count from completely different queries.
Personally I think it's a bit easier to read as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources