Count rows with condition - 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 2005 Forums

Transact-SQL (2005)

Count rows with condition

Author

Topic

kmorgan26
Starting Member

2 Posts

Posted - 2010-04-07 : 12:27:19

I have three tables: Table1 and Table2 and Table 3

Table 3 is a "status" table. 1=open, 2=working, 3=closed

SELECT T1.t1ID, T1.Title, COUNT(T2.t3ID) as myCount
FROM T1 LEFT OUTER JOIN
T2 ON T1.t1ID = T2.t1ID
GROUP BY t1.t1ID, t1.Title

This displays as such:

1 | title 1 | 4
2 | title 2 | 2
3 | title 3 | 0
4 | title 4 | 1

What I want to do is add a condition to the third column. I only want the items counted in the third column if they are NOT closed (ID of 3)

Thanks in advance for the help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-07 : 12:29:31

SELECT
T1.t1ID,
T1.Title,
--COUNT(T2.t3ID) as myCount
sum(case when T2.t3ID = 3 then 0 else 1 end) as myCount
FROM T1 LEFT OUTER JOIN
T2 ON T1.t1ID = T2.t1ID
GROUP BY t1.t1ID, t1.Title

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