Deeply understand the GROUP BY x, y statement in SQL

GROUP BY x statement in SQL is used to group records based on the common value of the specified column x. To extend this concept, GROUP BY x, y represents a finer granular grouping, where records are grouped not only based on the values ​​in column x, but also on the values ​​in column y.

How to work

This packet effectively divides the data into different sets. Each set contains records that have the same value for x and y. For example:

• GROUP BY SubjectClassify data based on unique values ​​in the Subject column.
• GROUP BY Subject, Semester further divides these groups according to the unique combination of values ​​in the Subject and Semester columns.

Example

Consider the following Subject_Selection table:

 --------- ---------- ---------- 
| Subject | Semester | Attendee |
 --------- ---------- ---------- 
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
 --------- ---------- ---------- 

Apply GROUP BY Subject to group subjects and calculate the number of attendees:

select Subject, Count(*)
from Subject_Selection
group by Subject

Output:

 --------- ------- 
| Subject | Count |
 --------- ------- 
| ITB001  |     5 |
| MKB114  |     2 |
 --------- ------- 

Extend to GROUP BY Subject, Semester:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

Output:

 --------- ---------- ------- 
| Subject | Semester | Count |
 --------- ---------- ------- 
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
 --------- ---------- ------- 

This result shows that three students took ITB001 in the first semester, two students took ITB001 in the second semester, and two students took MKB114 in the first semester.

By grouping multiple columns, you can extract more specific insights and analyze the data in more detail.