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

諾貝爾獎:群組函數

此教程有關群組函數，例如 COUNT, SUM 和 AVG.

練習

Using SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY.

找出總共有多少個獎頒發了。

SELECT COUNT(winner) FROM nobel

SELECT COUNT(winner) FROM nobel

列出每一個獎項(subject), 只列一次

SELECT DISTINCT subject FROM nobel

找出物理獎的總頒發次數。

nobel(yr, subject, winner)  

SELECT COUNT(subject) FROM nobel
  WHERE subject='Physics'

Using GROUP BY and HAVING.

對每一個獎項(Subject),列出頒發數目。

nobel(yr,subject, winner)  

SELECT subject, COUNT(winner)
  FROM nobel
 GROUP BY subject

對每一個獎項(Subject),列出首次頒發的年份。

nobel(yr, subject, winner)  

SELECT subject, MIN(yr)
  FROM nobel
 GROUP BY subject

對每一個獎項(Subject),列出2000年頒發的數目。

nobel(yr, subject, winner)  

SELECT subject,COUNT(yr)
FROM nobel
WHERE yr=2000
GROUP BY subject

深入探究群組函數和獨一DISTINCT.

對每一個獎項(Subject),列出有多少個不同的得獎者。

nobel(yr, subject, winner)  

SELECT subject,COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject

對每一個獎項(Subject),列出有多少年曾頒發過。

nobel(yr, subject, winner)  

SELECT subject,COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject

使用 HAVING.

列出哪年曾同年有3個物理獎Physics得獎者。

nobel(yr, subject, winner)  

SELECT yr
FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(winner)=3

列出誰得獎多於一次。

nobel(yr, subject, winner)  

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(yr) > 1

列出誰獲得多於一個獎項(Subject)

nobel(yr, subject, winner)  

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject)>1

GROUP BY yr, subject

哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。

nobel(yr, subject, winner)  

SELECT yr,subject
FROM nobel
WHERE yr>=2000
GROUP BY yr,subject
HAVING COUNT(winner)=3