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

SQL's GROUP BY with Multiple Columns: A Detailed Explanation

The SQL GROUP BY clause efficiently groups rows sharing common values, enabling aggregate function calculations on these groups. While single-column grouping is straightforward, multi-column grouping adds a layer of complexity and power.

Understanding GROUP BY x, y

The syntax GROUP BY x, y groups rows where both columns x and y have identical values. The aggregate functions in your query are then computed for each distinct combination of x and y values.

Illustrative Example

Let's examine the Subject_Selection table, tracking student attendance across subjects and semesters:

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

Grouping by Subject Only

The query:

<code class="language-sql">SELECT Subject, COUNT(*)
FROM Subject_Selection
GROUP BY Subject;</code>

Produces:

Subject	Count
ITB001	5
MKB114	2

This summarizes total attendance per subject.

Grouping by Subject and Semester

Now, consider this query:

<code class="language-sql">SELECT Subject, Semester, COUNT(*)
FROM Subject_Selection
GROUP BY Subject, Semester;</code>

This yields:

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

This provides a more granular view, showing attendance counts for each subject and semester combination. Notice how the results are now grouped by the unique pairings of Subject and Semester. This demonstrates the power of using multiple columns in the GROUP BY clause to achieve more refined data aggregation.

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