Difference between revisions of "SUM and COUNT/ja"
Kobashi.kaz (talk | contribs) |
Kobashi.kaz (talk | contribs) |
||
Line 35: | Line 35: | ||
</div> | </div> | ||
− | == | + | ==大陸のリスト== |
<div class='qu'> | <div class='qu'> | ||
大陸名を重複しないように表示。 | 大陸名を重複しないように表示。 |
Revision as of 16:55, 18 April 2018
Language: | [[:{{#invoke:String|sub|SUM and COUNT/ja
|1 |Expression error: Unrecognized punctuation character "{".}}|English]] |
---|
世界各国のプロフィール: 集計関数
このチュートリアルでは COUNT, SUM , AVG などの集計関数を扱う。集計関数は多数の値から1つの値を計算する。例えば、関数 SUM は 2, 4 ,5 から1つの値 11 を計算する。
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 |
... |
以下の例を見ておくとよい。
Using SUM, Count, MAX, DISTINCT and ORDER BY/ja.
Contents
世界人口のトータル
世界の総人口を表示。(各国の人口を合計)
world(name, continent, area, population, gdp)
SELECT SUM(population)
FROM world
SELECT SUM(population)
FROM world
大陸のリスト
大陸名を重複しないように表示。
SELECT DISTINCT(continent)
FROM world
GDP of Africa
Give the total GDP of Africa
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'
Count the big countries
How many countries have an area of at least 1000000
SELECT COUNT(name)
FROM world
WHERE area >= 1000000
Baltic states population
What is the total population of ('Estonia', 'Latvia', 'Lithuania')
SELECT SUM(population)
FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania')
Using GROUP BY and HAVING
You may want to look at these examples: Using GROUP BY and HAVING.
Counting the countries of each continent
For each continent show the continent and number of countries.
SELECT continent, COUNT(name)
FROM world
GROUP BY(continent)
Counting big countries in each continent
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)
Counting big continents
List the continents that have a total population of at least 100 million.
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>= 100000000
The nobel table can be used to practice more SUM and COUNT functions.