How Does GROUP BY x, y Granularly Group Data in SQL?

In-depth understanding of the GROUP BY x, y statement in SQL

The GROUP BY x statement in SQL is used to group records based on the common value of a specified column x. Extending this concept, GROUP BY x, y represents a more fine-grained grouping, where records are grouped not only based on the value in column x, but also based on the value in column y.

How it works

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

• GROUP BY SubjectCategories data based on unique values ​​in the Subject column.
• GROUP BY Subject, Semester further divides these groups based on unique combinations of values ​​in the Subject and Semester columns.

Example

Consider the following Subject_Selection table:

<code>+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+</code>

Apply GROUP BY Subject to group subjects and calculate attendance:

<code class="language-sql">select Subject, Count(*)
from Subject_Selection
group by Subject</code>

Output:

<code>+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+</code>

expands to GROUP BY Subject, Semester:

<code class="language-sql">select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester</code>

Output:

<code>+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+</code>

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 your data in greater detail.

The above is the detailed content of How Does GROUP BY x, y Granularly Group Data in SQL?. For more information, please follow other related articles on the PHP Chinese website!