Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

syntax error

Posted on 2014-08-25
11
Medium Priority
?
207 Views
Last Modified: 2014-08-29
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

0
Comment
Question by:al4629740
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 40284063
>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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40284073
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
 

Author Comment

by:al4629740
ID: 40284115
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:al4629740
ID: 40284122
There is definite data there fitting that criteria
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40284564
Prove it by posting some sample data and a query to load it.  Don't forget to include the table schema.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 40285070
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
 

Author Comment

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

Author Comment

by:al4629740
ID: 40285737
I would also like to have a one row count as a result
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40285805
"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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40286945
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40287043
And don't forget a query to load the sample data, no one likes typing.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question