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

al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Where ...  Min(h.activitydate) = 7
The Min() is the aggregate, which needs to be in a HAVING block.
SELECT SUM(h.hours) as Hours, MIN(h.ActivityDate) as Min_Date, MAX(h.ActivityDate) as Max_Date 
FROM tblOrgRegistrations AS r 
   JOIN tblOrgHours AS h ON h.RegID = r.RegID  AND Month(h.ActivityDate) = 7
   JOIN tblOrgActivities AS a ON h.ActivityID = a.ActivityID 
   JOIN tblOrgProfile AS o ON h.AgencyID = o.AgencyID 
Where o.AgencyID = 74 and 
Group By r.RegID,a.ActivityName, o.[Agency],h.Program, r.FirstName, r.LastName
HAVING Min(h.activitydate) = 7   -- <--  Looky here

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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?
0
al4629740Author Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
There is definite data there fitting that criteria
0
Anthony PerkinsCommented:
Prove it by posting some sample data and a query to load it.  Don't forget to include the table schema.
0
PortletPaulfreelancerCommented:
Classically a GROUP BY query has a set of non-aggregating fields and some aggregating columns, so you get a unique combination of the non-aggregating fields and for each of those the relevant aggregate values
SELECT
      r.RegID
    , a.ActivityName
    , o.[Agency]
    , h.Program
    , r.FirstName
    , r.LastName
    , 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
      INNER JOIN tblOrgActivities AS a ON h.ActivityID = a.ActivityID
      INNER JOIN tblOrgProfile AS o ON h.AgencyID = o.AgencyID
WHERE o.AgencyID = 74
GROUP BY
      r.RegID
    , a.ActivityName
    , o.[Agency]
    , h.Program
    , r.FirstName
    , r.LastName
HAVING
      MONTH(MIN(h.activitydate)) = 7 --<< but this is not good!
;

Open in new window

but you CANNOT have MIN(h.activitydate) in the where clause because MIN() is an aggregate,
An aggregate may not appear in the WHERE clause
and that aggregate value won't be know until AFTER the GROUP BY, but you CAN include aggregates in the HAVING clause because that is AFTER the GROUP BY
unless it is in a subquery contained in a HAVING clause
The balance of that error message is referring to using a subquery which isn't relevant here.

However we are left with 2 further problems:
A. Did you want more than 1 row as a result?

and

B.  MONTH(MIN(h.activitydate)) = 7 is probably not what you actually want. For example all these dates would have a month of 7

1884-07-27
2011-07-01
2012-07-01
2014-07-01
2037-07-16

so it's more than probable you would get unexpected results.

So, what were you trying to achieve by MONTH(MIN(h.activitydate)) = 7
0
al4629740Author Commented:
Related to another posted question...I would like to achieve MONTH(MIN(h.activitydate)) = 7 within a specified fiscal year range.
0
al4629740Author Commented:
I would also like to have a one row count as a result
0
PortletPaulfreelancerCommented:
"Related to another posted question" for the benefit of all
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28505261.html

neither question is yet fully answered and I'm off air now.
0
PortletPaulfreelancerCommented:
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
0
Anthony PerkinsCommented:
And don't forget a query to load the sample data, no one likes typing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.