# Difference between revisions of "The nobel table can be used to practice more SUM and COUNT functions."

 Language: English  • 日本語 • 中文

### Nobel Prizes: Aggregate functions

This tutorial concerns aggregate functions such as `COUNT`, `SUM` and `AVG`.

## Exercises

Show the total number of prizes awarded.

```SELECT COUNT(winner) FROM nobel
```
```SELECT COUNT(winner) FROM nobel
```

List each subject - just once

```SELECT DISTINCT subject FROM nobel
```

Show the total number of prizes awarded for Physics.

```nobel(yr, subject, winner)
```
```SELECT COUNT(subject) FROM nobel
WHERE subject='Physics'
```

Using GROUP BY and HAVING.

For each subject show the subject and the number of prizes.

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

For each subject show the first year that the prize was awarded.

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

For each subject show the number of prizes awarded in the year 2000.

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

Look into aggregates with DISTINCT.

Show the number of different winners for each subject.

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

For each subject show how many years have had prizes awarded.

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

Using HAVING.

Show the years in which three prizes were given for Physics.

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

Show winners who have won more than once.

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

Show winners who have won more than one subject.

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

GROUP BY yr, subject

Show the year and subject where 3 prizes were given. Show only years 2000 onwards.

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