asked on

syntax error

I have a syntax error below

Msg 147, Level 15, State 1, Line 9
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.

How do I resolve this?

SELECT SUM(h.hours) as Hours, MIN(h.ActivityDate) as Min_Date, MAX(h.ActivityDate) as Max_Date 
        FROM tblOrgRegistrations AS r 
             INNER JOIN tblOrgHours AS h 
                    ON h.RegID = r.RegID 
                   AND Month(h.ActivityDate) = 7
              INNER JOIN tblOrgActivities AS a 
                    ON h.ActivityID = a.ActivityID 
             INNER JOIN tblOrgProfile AS o 
                    ON h.AgencyID = o.AgencyID Where o.AgencyID = 74 and Min(h.activitydate) = 7
               Group By r.RegID,a.ActivityName, o.[Agency],h.Program, r.FirstName, r.LastName

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

btw in the above statement, since all of the SELECT columns are aggregates, then you don't need the GROUP BY line.
Unless ... do you wish to display those columns in the SELECT clause?

al4629740

ASKER

I am in the middle of modifying another sql statement...

So after looking at you

Open in new window

r posted code I changed it to the following and get no results at all

SELECT count(distinct RegID)
FROM tblOrgHours where fiscal = 2015 
HAVING  Month(Min(activitydate)) = 7

Open in new window

what am i doing wrong?

al4629740

ASKER

There is definite data there fitting that criteria

Anthony Perkins

Prove it by posting some sample data and a query to load it. Don't forget to include the table schema.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

al4629740

ASKER

Related to another posted question...I would like to achieve MONTH(MIN(h.activitydate)) = 7 within a specified fiscal year range.

al4629740

ASKER

I would also like to have a one row count as a result

PortletPaul

"Related to another posted question" for the benefit of all
https://www.experts-exchange.com/questions/28505261/Operand-type-clash-date-is-incompatible-with-tinyint.html

neither question is yet fully answered and I'm off air now.

PortletPaul

Please provide:

"sample data" does not have to be huge and it doesn't need anything private
"expected result" should be based on the sample data

This is a time proven method for getting you the results you want, quickly

Anthony Perkins

And don't forget a query to load the sample data, no one likes typing.