sql - 在多列上使用group by(Using group by on multiple columns)

Question

I understand the point of GROUP BY x

(我理解GROUP BY x的观点)

But how does GROUP BY x, y work, and what does it mean?

(但GROUP BY x, y如何运作的，它是什么意思？)

  ask by l--''''''---------'''''''''''' translate from so

Answer 1

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

(Group By X表示将所有具有相同X值的组合放入一组中 。)

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

(Group By X, Y表示将所有具有相同值的值放在一个组中的X和Y.)

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

(为了说明一个例子，假设我们有下表，与谁在大学的哪个科目上学习有关：)

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;

(仅在主题列上使用group by ;)

say:

(说：)

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

You will get something like:

(你会得到类似的东西：)

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

...because there are 5 entries for ITB001, and 2 for MKB114

(...因为ITB001有5个条目，MKB114有2个条目)

If we were to group by two columns:

(如果我们group by两列group by ：)

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

we would get this:

(我们会得到这个：)

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

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups" .

(这是因为，当我们按两列分组时，它会说“将它们分组，以便所有具有相同主题和学期的人在同一组中，然后计算所有聚合函数 （Count，Sum，Average等）。 ） 对于每个群体“ 。)

In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

(在这个例子中，事实表明，当我们计算它们时，有三个人在第一学期做ITB001， 两个人在第二学期做这个。做MKB114的人都在第一学期，所以没有第二学期的行（没有数据适合“MKB114，第二学期”组）)

Hopefully that makes sense.

(希望这是有道理的。)