error in sql syntax

Hi,

I have the following code: -

        sql = "SELECT score, avg(Score), TestID, DateTaken as 'Date Taken' " & _
                    "FROM Tests_Taken " & _
                    "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "' AND DateTaken between '" & Format(MonthCalendar1.SelectionStart, "yyyy-MM-dd") & "' and '" & Format(MonthCalendar2.SelectionStart, "yyyy-MM-dd") & "'" & _
                    " Group by TestID "

it keeps coming up with the error message -

Column 'Tests_Taken.Score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What have I done wrong please.

Many thanks
Lee
ljhodgettAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
YOu  have to put the fields that you aren't doing aggregations on the GROUP BY list...so,

  sql = "SELECT score, avg(Score), TestID, DateTaken as 'Date Taken' " & _
                    "FROM Tests_Taken " & _
                    "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "' AND DateTaken between '" & Format(MonthCalendar1.SelectionStart, "yyyy-MM-dd") & "' and '" & Format(MonthCalendar2.SelectionStart, "yyyy-MM-dd") & "'" & _
                    " Group by TestID, DateTaken "
0
 
chapmandewCommented:
Sorry...you need to take the score out of your select list...avg(score) is OK.
0
 
TextReportCommented:
When you are doing a GROUP BY you either have to include the field in the GROUP BY clause or you have to do an AGGREGATE on it, an Aggregate being SUM(), MIN(), MAX(), FIRST(), LAST(), AVG().

I have also added the vbcrlf to each line so when you are troubleshooting the SQL it is in a block rather than a single line, it is much easier to read then.

Finally if DateTaken includes the time then your BETWEEN will cause a problem for this reason I have changed it to >= AND < eg In Access Between Date() AND Date() will only include todays records where the time is midnight.

Cheers, Andrew


sql = "SELECT avg(Score), TestID, MIN(DateTaken) as 'Date Taken' " & vbcrlf & _
      "FROM Tests_Taken " & vbcrlf & _
      "WHERE UserID = '" & cmbUsers.SelectedValue.ToString & "'" & vbcrlf & _
      "AND DateTaken >= '" & Format(MonthCalendar1.SelectionStart, "yyyy-MM-dd") & "'" & vbcrlf & _
      "AND DateTaken <  '" & Format(MonthCalendar2.SelectionStart +1, "yyyy-MM-dd") & "'" & vbcrlf & _
      "GROUP BY TestID" & vbcrlf

Open in new window

0
All Courses

From novice to tech pro — start learning today.