2 counts in one query - 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)

2 counts in one query

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-09 : 05:00:59

Hello,

SELECT COUNT(status) AS A
FROM tblusers
WHERE status = 3

SELECT COUNt(status) AS B
FROM tblusers
WHERE status = 1

How do I combine these two? So I have only one query?
Result
A B
2 1

Thanxx
Neutcomp

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-09 : 05:34:10

[code]SELECT COUNT(CASE WHEN status = 3 THEN 1 END) AS A ,
COUNT(CASE WHEN status = 1 THEN 1 END) AS B
WHERE status IN (1, 3)
[/code]

Adding the WHERE doesn't change the result, but it may make the query faster by filtering the recordset before calculating the sums.

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-09 : 06:16:05

thanxx,

Final Query:
SELECT COUNT(CASE WHEN status = 3 THEN 1 END) AS A ,
COUNT(CASE WHEN status = 1 THEN 1 END) AS B
From tblusers
WHERE status IN(1, 3)

You forgot the From line.

Cya
Bjorn
8-D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources