[RESOLVED] Multiple Count(*) in SELECT

[conwa02976]

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.

[jmcilhinney]

There's no way to create a single result set with two different WHERE clauses. COUNT tells you how many records are in a group so how can the same group have two different numbers of records in it? If you want two different WHERE clauses then you need two different queries. You can perform a UNION to concatenate the two result sets but then you'll have each group in the final result set twice.

[FunkyDexter]

You can use a case statement for this:-

SELECT Year(tblCSSData.SurveyDate) as [Year], 
Sum(Case When tblCSSData.Factor = 0 then 0 else 1 end) As Count,
Sum(tblCSSData.Factor)/Sum(Case When tblCSSData.Factor = 0 then 0 else 1 end) as AvgScore,
tblCSSData.StmtNum as StmtNum,
Count(*) as TotalCount
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

Basically, what I've done is removed tblCSSData.Factor <> 0 from the where clause so these records will be returned and included in the Total Count figure.

The Count figure excludes them using the case statement (it only counts values where tblCSSData.Factor <> 0).

You can't use the AVG function anymore because you don't want it to include records where tblCSSData.Factor = 0 so I've had to write the calculation in full. We only need to exclude from the denominator because adding a value of 0 to the numerator doesn't affect it.

[conwa02976]

I got the answer from another forum and it was the same thought process as FunkyDexter. Just adding a couple of case statements made all of the difference. Here is the solution that worked:

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

I will mark this as resolved!!

Thanks all!!

[FunkyDexter]

Avg(CASE tblCSSData.Factor when 0 then NULL else tblCSSData.Factor END)

I don't think that'll work. Any calculation containing a null results in a null, regardless of what other values are included. I haven't tested it with AVG but that's certainly true of SUM.