x

Group By syntax error - Incorrect syntax near the keyword 'Group'.

Hello,

I am getting a syntax error near Group By. I just can not figure out what's wrong with. Error Message: Msg 156, Level 15, State 1, Line 25

Incorrect syntax near the keyword 'Group'.

UPDATE Tracker_StudentCourseInformation SET Tracker_StudentCourseInformation.CompletionStatus = MAX(TSLI.CompletionStatus), Tracker_StudentCourseInformation.ExpirationDate = MAX(TSLI.ExpirationDate), Tracker_StudentCourseInformation.FirstCompletionDate = MAX(TSLI.FIRSTCOMPLETIONDATE), Tracker_StudentCourseInformation.MostRecentCompletionDate = MAX(TSLI.MostRecentCompletionDate), --Tracker_StudentCourseInformation.IndivCommentForStudent = @COMMENT_FOR_STUD, Tracker_StudentCourseInformation.CurrencyDaysFlag_Critical = MAX(TL.CurrencyDaysFlag_Critical), Tracker_StudentCourseInformation.CurrencyDaysFlag_Alert = MAX(TL.CurrencyDaysFlag_Alert) FROM Tracker_StudentLessonInformation TSLI, -- to get unique lesson count per course (number of lesson with in a course) (Select CourseID, Count(Distinct LessonID) LessonCNT From MAXTRAX_COURSELESSON_SYNC_UPLOAD Group By CourseID) CRS,
MAXTRAX_COURSELESSON_SYNC_UPLOAD MCSU, TRACKER_LESSONS TL WHERE EXISTS (SELECT * FROM Tracker_StudentCourseInformation TSCI WHERE TSCI.StudentID = TSLI.StudentID AND TSCI.CourseID = CRS.CourseID) AND TSLI.LessonID = MCSU.LessonID AND CRS.CourseID = MCSU.CourseID AND TSLI.LessonID = TL.LessonID AND TSLI.StudentID = 7 AND TSLI.CompletionStatus = 'completed'

Group By  CRS.CourseID, TSLI.studentid, CRS.LessonCNT                    

-- returns only the studnets with the completed number of lessons count 'equals to/same -- as' the number of the lessons with in a course. -- Having CRS.LessonCNT = Count(Distinct MCSU.LessonID)

Having CRS.LessonCNT = Count(Distinct TSLI.LessonID)                    

-- Order By TSLI.StudentID, CRS.CourseID

Thanks in advance, SP.

more ▼

asked May 14, 2010 at 01:30 PM in Default

avatar image

gnitha
1 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The GROUP by is not valid in an UPDATE statement. Did you intend it to be part of one of the SELECT statements (the derived table or the exists clause)?

more ▼

answered May 14, 2010 at 06:38 PM

avatar image

Kevin Snow
1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2036

asked: May 14, 2010 at 01:30 PM

Seen: 2938 times

Last Updated: May 14, 2010 at 01:30 PM

Copyright 2018 Redgate Software. Privacy Policy