May 3, 2009 at 7:05 pm
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:
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.
May 3, 2009 at 7:59 pm
How about something like this. I am taking the condition out of the Where clause and using a Case statement to determine the count and avg.
SELECT Year(tblCSSData.SurveyDate) as [Year],
COUNT(*) as [TotalCount],
SUM(CASE tblCSSData.Factor when 0 then 0 else 1 end) [Count],
Avg(CASE tblCSSData.Factor when 0 then NULL else tblCSSData.Factor END) 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')
GROUP BY Year(tblCSSData.SurveyDate), tblCSSData.StmtNum
May 4, 2009 at 5:54 am
Thanks Ken, it worked like a charm!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply