issue with SQL command

Question

I'm getting an exception when trying to run my code, I'm fairly new to SQL not sure if I've used the right syntax.

"Column 'news.score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." System.Data.SqlClient.SqlException

foreach(PatientModel a in patientList)
    {
         try
         {
             con.Open();
             cmd.CommandText = "SELECT MAX(datetime), score FROM news WHERE(patientID = @patientID)";
             cmd.Parameters.AddWithValue("@patientID", a.PatientID);
             reader = cmd.ExecuteReader();

             while (reader.Read())
             {
                 Console.WriteLine("");
                 a.Score = (int)reader["score"];
             }
         }
         catch
         {
             con.Close();
         }
    }
con.Close();```

Try adding `GROUP BY score` into the sql command. `cmd.CommandText = @"SELECT MAX(datetime), score FROM news WHERE (patientID = @patientID) GROUP BY score";` — Dmitry Bychenko, Mar 12 '21 at 20:57
You have to ask yourself what that means, though. Does it make sense to get the maximum `datetime` for all rows that have each `score` value? That's what adding `GROUP BY score` will do. Is that what you want? If not, what were you expecting to get? — madreflection, Mar 12 '21 at 21:00
This approach to database access is *very old.* Consider a more modern approach like Dapper, or even Entity Framework Core. There are many benefits, not the least of which is much simpler code. — Robert Harvey, Mar 12 '21 at 21:05

DotNetDublin · Accepted Answer · 2021-03-12T21:02:57.397

0

Try

SELECT MAX(datetime), score FROM news WHERE patientID = @patientID GROUP BY score

https://www.w3schools.com/sql/sql_groupby.asp

edited Mar 12 '21 at 21:02

answered Mar 12 '21 at 21:00

DotNetDublin

770
1
9
23

issue with SQL command

1 Answers1