Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Stored Procedure - Field not in aggregate function or group by

    Hope someone can help me work out the best solution for this stored procedure query.

    I'm getting the following error:
    Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    Here is my select query:
    Code:
        SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate
        FROM   dbo.Applicants
        WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr)
        ORDER BY SubmitDate
    The submitted field is a date field.
    I don't want to add the submitted field to Group By as I want to group by month not date.

    Is there any solution to avoid grouping by date?

    Thanks,
    <- Hides behind a rock.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by KevCB226 View Post
    I don't want to add the submitted field to Group By as I want to group by month not date.
    if you want to group by month, then you need to group by month
    Code:
    GROUP BY DATENAME(MONTH, submitted)
    and at that point you need to remove the DISTINCT as it's redundant with GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks, is there any difference between using your solution and the following which also seems to work:

    Code:
    GROUP BY { fn MONTHNAME(submitted) }
    <- Hides behind a rock.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm surprised that works

    i've never seen curly braces in sql before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •