mysql count的用法_155、MySQL入门（五）：SUM and COUNT用法

Sqlzoo习题练习：SUM and COUNT

下面为SUM and COUNT习题内容：

知识点：

SUM() 函数

SUM 函数返回数值列的总数(总额)。

SUM() 语法

SELECT SUM(column_name) FROM table_name

--#1

/*

Show the total population of the world.

表world包含的字段：

world(name, continent, area, population, gdp)

*/

SELECT SUM(population)

FROM world

知识点：

SELECT DISTINCT 语句

关键词 DISTINCT 用于返回唯一不同的值。

语法：SELECT DISTINCT 列名称 FROM 表名称

--#2

/*

List all the continents - just once each.

*/

SELECT DISTINCT(continent)

FROM world

--#3

/*

Give the total GDP of Africa

*/

SELECT SUM(GDP) FROM world

WHERE continent = 'Africa'

--#4

/*

How many countries have an area of at least 1000000

*/

SELECT COUNT(name) FROM world

WHERE area >= 1000000

--#5

/*

What is the total population of ('Estonia', 'Latvia', 'Lithuania')

*/

SELECT SUM(population) FROM world

WHERE name IN ('Estonia', 'Latvia', 'Lithuania')

知识点：

GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

GROUP BY 语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

--#6

/*

For each continent show the continent and number of countries.

*/

SELECT continent,COUNT(name)

FROM world

GROUP BY continent

--#7

/*

For each continent show the continent and number of countries with populations of at least 10 million.

*/

SELECT continent,COUNT(name)

FROM world

WHERE population > 10000000

GROUP BY continent

知识点：

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

HAVING 语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

--#8

/*

List the continents that have a total population of at least 100 million.

*/

SELECT continent

FROM world

GROUP BY continent

HAVING SUM(population) > 100000000