CODEWITHSUNDEEP
sql-servert-sqlaggregatewhere-clause
Slinky
Slinky

Reputation: 5832

TSQL - Aggregates in HAVING Clause

I know this problem has been asked a lot but when I address the error message and use a HAVING clause, I am still receiving the dreaded:

An aggregate may not appear in the WHERE clause unless it is in a 
subquery contained in a HAVING clause or a select list, 
and the column being aggregated is an outer reference.

What am I doing wrong, here?

SELECT
mr.ClubKeyNumber,
COUNT(mr.MonthlyReportID),
SUM(CONVERT(int,mr.Submitted))
FROM MonthlyReport mr
WHERE mr.ReportYear = 2014
AND COUNT(mr.MonthlyReportID) = 12
GROUP BY mr.ClubKeyNumber
HAVING (SUM(CONVERT(int,mr.Submitted))) > 11

Upvotes: 3

Views: 432

Answers (1)

EoinS
EoinS

Reputation: 5482

The problem isn't with your HAVING clause it's in your WHERE clause.

You have an aggregate count in your where clause, try this: 

SELECT
mr.ClubKeyNumber,
COUNT(mr.MonthlyReportID),
SUM(CONVERT(int,mr.Submitted))
FROM MonthlyReport mr
WHERE mr.ReportYear = 2014
GROUP BY mr.ClubKeyNumber
HAVING (SUM(CONVERT(int,mr.Submitted))) > 11 and COUNT(mr.MonthlyReportID) = 12

The where clause checks each row being aggregated before the group by clause. It cannot count your MonthlyReportID until after the group by so move it to the having clause.

Here is a simple example you can play with to demonstrate where vs have.

Upvotes: 7

Related Questions