SQL WHERE clause issues

The problem I am having seems to come from my where clause and hardcoding. I have two tables one with ID's and another with ID's and Scores for the ID's.

So my code looks a little something like this:

SELECT AVG(CAST(c.Score AS DEC(10,2))) AS avgTestC,
       AVG(CAST(d.Score AS DEC(10,2))) AS avgTestD,
       AVG(CAST(e.Score AS DEC(10,2))) AS avgTestE,
       AVG(CAST(f.Score AS DEC(10,2))) AS avgTestF,
       COUNT(DISTINCT c.ID) AS CountC,
       COUNT(DISTINCT d.ID) AS CountD,
       COUNT(DISTINCT e.ID) AS CountE,
       COUNT(DISTINCT f.ID) AS CountF
FROM tblWithIds a,
     JOIN tblScores b ON a.ID = b.ID AND b.Year = @Year
     LEFT JOIN tblScores c ON a.ID = c.ID AND c.Year = @Year
     LEFT JOIN tblScores d ON a.ID = d.ID AND d.Year = @Year
     LEFT JOIN tblScores e ON a.ID = e.ID AND e.Year = @Year
     LEFT JOIN tblScores f ON a.ID = f.ID AND f.Year = @Year
WHERE c.TestC = 'Test C'
      d.TestD = 'Test D'
      e.TestE = 'Test E'
      f.TestF = 'Test F'

Now the problem is that when I add more to the where clause and almost identical "Tests" the where clause, it gives me NULL values for everything and 0's for the COUNT. The code above works properly but the test am I using have very similar names and the table was made poorly so all of the "Tests" names' are all in one column. I cannot debug as I am stuck using MSSQL 05 for now. Please help!

EDIT: After getting using the aggregate function SUM below from EricZ, if anyone else is interested I have found that

AVG( CASE WHEN b.Test = 'Test D' THEN CAST(b.Score AS DEC(10,2)) ELSE NULL END) AS avgTestC

to be the correct form for the AVG aggregate func开发者_Python百科tion.

---

You can use CASE to do COUNT, and just need JOIN table once

SELECT 
       AVG(CAST((CASE WHEN b.TestC = 'Test C' THEN b.Score ELSE 0 END) AS DEC(10,2))) AS avgTestC,
       AVG(CAST((CASE WHEN b.TestD = 'Test D' THEN b.Score ELSE 0 END) AS DEC(10,2))) AS avgTestD,
       AVG(CAST((CASE WHEN b.TestE = 'Test E' THEN b.Score ELSE 0 END) AS DEC(10,2))) AS avgTestE,
       AVG(CAST((CASE WHEN b.TestF = 'Test F' THEN b.Score ELSE 0 END) AS DEC(10,2))) AS avgTestF,             
       SUM(CASE WHEN b.TestC = 'Test C' THEN 1 ELSE 0 END) AS CountC,
       SUM(CASE WHEN b.TestD = 'Test D' THEN 1 ELSE 0 END)  AS CountD,
       SUM(CASE WHEN b.TestE = 'Test E' THEN 1 ELSE 0 END)  AS CountE,
       SUM(CASE WHEN b.TestF = 'Test F' THEN 1 ELSE 0 END)  AS CountF
FROM tblWithIds a,
JOIN tblScores b ON a.ID = b.ID AND b.Year = @Year

---

Use CASE instead

SELECT SUM(CASE WHEN Something > 0 THEN Something ELSE 0 END) AS A, SUM(CASE WHEN Something2 > 0 THEN Something2 ELSE 0 END) AS B