Counting and averaging
Databases & SQL Lesson 6 2:01 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Sometimes you do not want the rows, you want a summary of them.
有时候你要的不是那些行本身，而是对它们的一个汇总。
An aggregate function does that, and it changes the shape of the answer: five rows go in and one row comes out.
聚合函数做的就是这件事，而它改变了答案的形状： 五行进去，一行出来。
Say that again, because it is the part students trip on — COUNT star gives you one number, not five answers, and the result table is a single cell.
再说一遍，因为这正是学生会绊倒的地方—— COUNT(*) 给你一个数字，不是五个答案， 而那张结果表只有一个单元格。
There are five to learn, and here they all are on the same column so you can check every one of them by eye.
要学的有五个，这里它们全都作用在同一列上， 这样你可以用眼睛把每一个都核对一遍。
COUNT star is five, the number of rows.
COUNT(*) 是 5，也就是行数。
SUM is four hundred and seven.
SUM 是 407。
AVG is eighty-one point four.
AVG 是 81.4。
MIN is sixty-four and MAX is ninety-five.
MIN 是 64，MAX 是 95。
Four of them need a column named; only COUNT star does not.
其中四个都需要指定一列；只有 COUNT(*) 不需要。
One distinction worth knowing.
有一个区别值得知道。
COUNT star counts rows, whatever is in them.
COUNT(*) 数的是行，不管里面装了什么。
COUNT of a column counts VALUES in that column, and a NULL is not a value — so it skips that row.
COUNT(某一列) 数的是那一列里的"值"，而 NULL 不是一个值—— 所以它会跳过那一行。
Three customers, but only two have an email, so the two queries give different numbers on the same table.
三个客户，但只有两个有邮箱， 所以这两条查询在同一张表上给出不同的数字。
Finally, name your answers.
最后，给你的答案起个名字。
Without AS, the heading of the result is the expression you typed, brackets and all.
不写 AS，结果的标题就是你敲进去的那个表达式，连括号一起。
With AS, it is a readable heading — and if a task or a report asks for a column called average, that is not cosmetic, it is the answer.
写了 AS，它就是一个能读的标题—— 而如果一道题或一份报表要求一列叫 average， 那就不是装饰，那就是答案本身。
ROUND is worth pairing with AVG for the same reason.
出于同样的理由，ROUND 值得和 AVG 配在一起用。
Four things to take with you.
带走四点。
One: an aggregate turns many rows into one row.
第一：聚合函数把很多行变成一行。
Two: COUNT, SUM, AVG, MIN and MAX are the five.
第二：COUNT、SUM、AVG、MIN、MAX 就是那五个。
Three: COUNT star counts rows while COUNT of a column skips NULLs.
第三：COUNT(*) 数行，而 COUNT(某列) 会跳过 NULL。
Four: AS gives the answer a readable heading.
第四：AS 给答案一个能读的标题。
Now run the tasks below.
现在去做下面的题。