world表：

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000
… … … … …

name: 国家名称
continent: 洲
area: 面积
population: 人口
gdp: 国内生产总值

SUM and COUNT/zh

1
2
3
1.展示世界的總人口。
SELECT sum(population)
FROM world
1
2
2.列出所有的洲份, 每個只有一次。
select distinct continent from world
1
2
3
3.找出非洲(Africa)的GDP總和。
select sum(gdp) from world
where continent = 'Africa'
1
2
3
4.有多少個國家具有至少百萬(1000000)的面積。
select count(name) from world
where area >= 1000000
1
2
3
5.('France','Germany','Spain')（“法國”，“德國”，“西班牙”）的總人口是多少？
select sum(population) from world
where name in ('France','Germany','Spain')
1
2
3
6.對於每一個洲份，顯示洲份和國家的數量。
select continent,count(name) from world
group by continent
1
2
3
4
7.對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。
select continent,count(name) from world
where population >= 10000000
group by continent
1
2
3
4
8.列出有至少100百萬(1億)(100,000,000)人口的洲份。
select continent from world
group by continent
having sum(population) >= 100000000

The nobel table can be used to practice more SUM and COUNT functions./zh

1
2
1.找出總共有多少個獎頒發了。
SELECT COUNT(winner) FROM nobel
1
2
2.列出每一個獎項(subject), 只列一次
select distinct subject from nobel
1
2
3
3.找出物理獎的總頒發次數。
select count(yr) from nobel
where subject = 'Physics'
1
2
3
4.對每一個獎項(Subject),列出頒發數目。
select subject,count(yr) from nobel
group by subject
1
2
3
5.對每一個獎項(Subject),列出首次頒發的年份。
select subject,min(yr) from nobel
group by subject
1
2
3
4
6.對每一個獎項(Subject),列出2000年頒發的數目。
select subject,count(winner) from nobel
where yr = 2000
group by subject
1
2
3
7.對每一個獎項(Subject),列出有多少個不同的得獎者。
select subject,count(distinct winner) from nobel
group by subject
1
2
3
8.對每一個獎項(Subject),列出有多少年曾頒發過。
select subject,count(distinct yr) from nobel
group by subject
1
2
3
4
5
9.列出哪年曾同年有3個物理獎Physics得獎者。
select yr from nobel
where subject = 'Physics'
group by yr
having count(winner) = 3
1
2
3
4
10.列出誰得獎多於一次。
select winner from nobel
group by winner
having count(yr) > 1
1
2
3
4
11.列出誰獲得多於一個獎項(Subject)
select winner from nobel
group by winner
having count(distinct subject) > 1
1
2
3
4
5
12.哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。
select yr,subject from nobel
where yr >= 2000
group by yr,subject
having count(winner) = 3

Quiz

_5X1B_63_NIY~__@O_P_7VX.png
1.Select the statement that shows the sum of population of all countries in ‘Europe’
Answer：

1
SELECT SUM(population) FROM bbc WHERE region = 'Europe'

2.Select the statement that shows the number of countries with population smaller than 150000
Answer：

1
SELECT COUNT(name) FROM bbc WHERE population < 150000

3.Select the list of core SQL aggregate functions
Answer：

1
AVG(), COUNT(), MAX(), MIN(), SUM()

4.Select the result that would be obtained from the following code:

1
2
3
4
SELECT region, SUM(area)
  FROM bbc 
 WHERE SUM(area) > 15000000 
 GROUP BY region

Answer：

1
No result due to invalid use of the WHERE function

5.Select the statement that shows the average population of ‘Poland’, ‘Germany’ and ‘Denmark’
Answer：

1
SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')

6.Select the statement that shows the medium population density of each region
Answer：

1
SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region

7.Select the statement that shows the name and population density of the country with the largest population
Answer：

1
SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)

8.Pick the result that would be obtained from the following code:

1
2
3
4
SELECT region, SUM(area) 
  FROM bbc 
 GROUP BY region 
 HAVING SUM(area)<= 20000000

Answer：
Q6YSU_E4_7__BQELH5IK@1W.png

Summary

  • 聚合函数将多个数据进行计算，得出的结果只有一个数据，内部可以使用distinct去掉重复值
  • 使用group by后，前面的select只能是分组条件或是使用聚合函数
  • where和having，group by前的where是分组之前的筛选，having是分组后要进行的操作
  • 可以进行多次分组，例如每个x下的每个y，可以用group by x,y