asked on

SQL Error with aggregate function

Hi

In the SQL query below I get the following error and can't understand why: "Column vPerformanceHolesDelaysSurnames.Date" is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause

SELECT Max([vPerformanceHolesDelaysSurnames].[Date]) As [LatestDate], [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number]
FROM [vPerformanceHolesDelaysSurnames]
WHERE ( [vPerformanceHolesDelaysSurnames].[Foreman] = 'masimong air' )
 Group By [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number]
HAVING ( [vPerformanceHolesDelaysSurnames].[Date] >= '01 Oct 2017' And [vPerformanceHolesDelaysSurnames].[Date] <= '17 Oct 2017' )

Open in new window

PortletPaul

Use those condition in the WHERE clause:

SELECT
      MAX([vPerformanceHolesDelaysSurnames].[Date]) AS [LatestDate]
    , [vPerformanceHolesDelaysSurnames].[Shaft]
    , [vPerformanceHolesDelaysSurnames].[Machine number]
FROM [vPerformanceHolesDelaysSurnames]
WHERE [vPerformanceHolesDelaysSurnames].[Foreman] = 'masimong air'
AND [vPerformanceHolesDelaysSurnames].[Date] >= '01 Oct 2017'
AND [vPerformanceHolesDelaysSurnames].[Date] <= '17 Oct 2017')
GROUP BY
      [vPerformanceHolesDelaysSurnames].[Shaft]
    , [vPerformanceHolesDelaysSurnames].[Machine number]

Open in new window

The HAVING clause is designed to filter results on the AGGREGATED values. None of the conditions in that original query for that clause requires any aggregation, so do not use the having clause for those.

Additionally the [Date] column isn't recognized by the having clause because it isn't listed in the group by clause; simply another reason to use WHERE for those conditions.

Murray Brown

ASKER

Thanks. I though that because the [Date] column is an aggregate (MAX) that it has to appear in the HAVING part of the statement

ASKER CERTIFIED 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

Murray Brown

ASKER

Thank you very much. That is a great help

PortletPaul

A couple of other tips/pointers is I may:

table aliases can really help to make SQL easier to read
the best/safest date literals in SQL Server are in YYYYMMDD format (I am not sure how/if '17 Oct 2017' will work)
be very careful about using <= in a date range (look at what I did carefully)
e.g.

SELECT
      MAX(snames.[Date]) AS [LatestDate]
    , snames.[Shaft]
    , snames.[Machine number]
FROM [vPerformanceHolesDelaysSurnames] AS snnames
WHERE snames.[Foreman] = 'masimong air'
AND snames.[Date] >= '20171001'
AND snames.[Date] < '20171018')
GROUP BY
      snames.[Shaft]
    , snames.[Machine number]

Open in new window

Murray Brown

ASKER

Thank you very much. I appreciate the advice