select A.grading, CASE WHEN count(B.IDno) = 0 THEN count(C.IDno) ELSE COUNT(B.IDno) end as myCountFrom A
left join B on A.fkIDno = B.IDno and A.grading = 1
left join C on A.fkIDno = C.IDno and A.grading = 2
Where A.grading > 3

select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount
From A
left join B on A.fkIDno = B.IDno and A.grading = 1
left join C on A.fkIDno = C.IDno and A.grading = 2
Where A.grading > 3

Jeff Moden (9/16/2009)

---

Matt is certainly on the right track... but you don't need a CASE statement...

select A.grading, ISNULL(NULLIF(count(B.IDno),0),COUNT(B.IDno)) as myCount
From A
left join B on A.fkIDno = B.IDno and A.grading = 1
left join C on A.fkIDno = C.IDno and A.grading = 2
Where A.grading > 3

As a sidebar, I used ISNULL instead of COALESCE because ISNULL is a bit faster across a million rows. If you believe in the MYTH of code portability, then you should change it back to COALESCE.

select A.grading, (1-SIGN(COUNT(B.IDno)))*COUNT(C.IDno) + COUNT(B.IDno)  as myCount
From A
left join B on A.fkIDno = B.IDno and A.grading = 1
left join C on A.fkIDno = C.IDno and A.grading = 2
Where A.grading > 3