CODEWITHSUNDEEP
sqlvisual-studioquery-builder
user6877804
user6877804

Reputation:

C# Query Builder SQL statement not working. How do I fix it?

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

Upvotes: 0

Views: 380

Answers (1)

smoksnes
smoksnes

Reputation: 10851

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

Upvotes: 2

Related Questions