I'm trying to GROUP BY in a SQL statement using Query Builder in VS 2015, but it's not working... Can you please tell me what's wrong? Thank you! :)
SELECT students_details.class AS Class, students.name AS Name, students.guardian AS Gurdian, students.phone AS Phone, students.mobile AS Mobile
FROM students_det INNER JOIN students ON students_details.studentID = students.studentID
ORDER BY Class
GROUP BY Class
If you're using GROUP BY, you may only select the columns you're grouping by or aggregation functions. More info in this question. Or as MSDN puts it:
A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.
So in your case you can only select Class, because each row will be grouped by Class. You can look at it as a Dictionary<string, List<Row>> in C# where the string here is the different classes and the Row is the rest of the rows. Or like a two dimensional array.
What you probably want to do is to simply order by class (without group by), or do an aggregation function on class. Such as, how many students are there in each class?
SELECT students_details.class AS Class, count(*) AS NumberOfStudents
FROM students_det INNER JOIN students ON students_details.studentID = students.studentID
ORDER BY Class
GROUP BY Class
