Microsoft SQL Server GROUP BY Group by with multiple tables, multiple columns
Example
Group by is often used with join statement. Let's assume we have two tables. The first one is the table of students:
| Id | Full Name | Age |
|---|---|---|
| 1 | Matt Jones | 20 |
| 2 | Frank Blue | 21 |
| 3 | Anthony Angel | 18 |
Second table is the table of subject each student can take:
| Subject_Id | Subject |
|---|---|
| 1 | Maths |
| 2 | P.E. |
| 3 | Physics |
And because one student can attend many subjects and one subject can be attended by many students (therefore N:N relationship) we need to have third "bounding" table. Let's call the table Students_subjects:
| Subject_Id | Student_Id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 2 | 1 |
| 3 | 2 |
| 1 | 3 |
| 1 | 1 |
Now lets say we want to know the number of subjects each student is attending. Here the standalone GROUP BY statement is not sufficient as the information is not available through single table. Therefore we need to use GROUP BY with the JOIN statement:
Select Students.FullName, COUNT(Subject Id) as SubjectNumber FROM Students_Subjects
LEFT JOIN Students
ON Students_Subjects.Student_id = Students.Id
GROUP BY Students.FullName
The result of the given query is as follows:
| FullName | SubjectNumber |
|---|---|
| Matt Jones | 3 |
| Frank Blue | 2 |
| Anthony Angel | 1 |
For an even more complex example of GROUP BY usage, let's say student might be able to assign the same subject to his name more than once (as shown in table Students_Subjects). In this scenario we might be able to count number of times each subject was assigned to a student by GROUPing by more than one column:
SELECT Students.FullName, Subjects.Subject,
COUNT(Students_subjects.Subject_id) AS NumberOfOrders
FROM ((Students_Subjects
INNER JOIN Students
ON Students_Subjcets.Student_id=Students.Id)
INNER JOIN Subjects
ON Students_Subjects.Subject_id=Subjects.Subject_id)
GROUP BY Fullname,Subject
This query gives the following result:
| FullName | Subject | SubjectNumber |
|---|---|---|
| Matt Jones | Maths | 2 |
| Matt Jones | P.E | 1 |
| Frank Blue | P.E | 1 |
| Frank Blue | Physics | 1 |
| Anthony Angel | Maths | 1 |
