Using group by on multiple columns

Question

I recognize the purpose of GROUP BY x.

However, how do GROUP BY X, Y operate and what does it signify?

Answer 1

Group By X means to put all those with the same value for X in one group.

Group By X, Y means to put all those with the same values for both X and Y in one group.

Let's use the following table, which pertains to which university students are enrolled in which subjects, as an illustration:

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

When you use a group by on the subject column only; say:

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

Output:

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

Unlock the power of data and embark on a journey towards becoming a skilled data scientist. Join our comprehensive Data Science Training program today!

Answer 2

Let me give you an example. A class has 50 students and out of which 20 is male and 30 is female. Now you want to know how many students passed/failed the exam by gender. 

count of students  - Group by gender 

Gender	No of students
Female	30
Male	20

count of students  - Group by gender, Status 

It first groups by gender and then groups by status

Gender	Exam status	No of students
female	pass	20
female	fail	10
male	pass	15
male	fail	5

count of students  - Group by Status, gender

It first groups by status and then groups by gender

Status	Gender	No of students
Pass	female	20
Pass	male	15
fail	female	10
fail	male	5