How Does SQL's `GROUP BY` Function Work with Multiple Columns?

SQL's GROUP BY Clause: Handling Multiple Columns

The SQL GROUP BY clause aggregates rows in a table based on specified columns. Let's explore how it functions with single and multiple columns.

Single-Column Grouping (GROUP BY x)

GROUP BY x groups rows sharing the same value in column 'x'. All rows with identical 'x' values are treated as a single group.

Multi-Column Grouping (GROUP BY x, y)

Extending this, GROUP BY x, y groups rows based on matching values in both columns 'x' and 'y'. Rows must have identical values for both 'x' and 'y' to be in the same group.

Example: Subject Enrollment

Consider the Subject_Selection table tracking student subject enrollment:

<code>Table: Subject_Selection

+---------+----------+---------+
| 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>

Grouping by Subject Only

The query SELECT Subject, COUNT(*) FROM Subject_Selection GROUP BY Subject groups by Subject:

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

This shows the total enrollment for each subject.

Grouping by Subject and Semester

The query SELECT Subject, Semester, COUNT(*) FROM Subject_Selection GROUP BY Subject, Semester groups by Subject and Semester:

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

This provides enrollment counts for each subject per semester, offering a more granular view. The key difference is the added level of detail provided by including Semester in the GROUP BY clause.

The above is the detailed content of How Does SQL's `GROUP BY` Function Work with Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!