I am using SQL Server 2005 and I need to figure out how to get multiple Count(*) results from one SELECT with two different WHERE clauses. Here is one SQL that works as desired:
Code:
SELECT Year(tblCSSData.SurveyDate) as [Year], Count(*) as Count, Avg(tblCSSData.Factor) as AvgScore,
     tblCSSData.StmtNum as StmtNum
FROM tblCSSData INNER JOIN tblStatements ON tblCSSData.StmtNum = tblStatements.StmtNum
WHERE (tblCSSData.SurveyNum = 1722) AND (tblStatements.Count_Range = 'R') AND (tblCSSData.Factor <> 0)
GROUP BY Year(tblCSSData.SurveyDate), tblCSSData.StmtNum
From this I get the desired results of:
2008 116 91.6375 3917
2008 98 89.0324 3920
etc...

I would like to have one more column named TotalCount in the same SELECT statement as above but without the AND (tblCSSData.Factor <> 0) part.

I would like to get this:
2008 116 91.6375 3917 120
2008 98 89.0324 3920 105

Is this possible?

Thanks in advance.