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

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

## 練習

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

```SELECT DISTINCT subject FROM nobel
```

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

Using GROUP BY and HAVING.

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

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

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

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

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

```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
```

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

GROUP BY yr, subject

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