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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.