I need to compute how many different types of fruit were sold in each county within a certain date range, using `county_name` and distinct `fruit_code`s.

I got a `county` table:

| county\_code | county\_name |
| --- | --- |
| 1 | Colfax |
| 2 | Catron |
| 3 | Harding |

And I got a `fruit` table:

| county\_code | fruit\_code | date |
| --- | --- | --- |
| 1 | 1 | 2020-01-01 |
| 1 | 2 | 2020-01-01 |
| 1 | 3 | 2020-01-01 |
| 1 | 4 | 2020-01-01 |
| 2 | 1 | 2020-01-01 |
| 2 | 2 | 2020-01-01 |
| 3 | 1 | 2020-01-01 |
| 3 | 2 | 2020-01-01 |
| 3 | 3 | 2020-01-01 |
| 1 | 1 | 2021-01-01 |
| 1 | 3 | 2021-01-01 |
| 2 | 2 | 2021-01-01 |
| 3 | 2 | 2021-01-01 |
| 3 | 3 | 2021-01-01 |

So my expected result would be:

| county\_name | fruit\_code |
| --- | --- |
| Colfax | 4 |
| Catron | 2 |
| Harding | 3 |

I tried the following code, but the number of distinct `fruit_code`s is not exactly the correct one, unfortunately (note: the actual table is much longer, of course).

```
WITH unique_fruits AS (
    SELECT fruit_code FROM fruit
    WHERE date BETWEEN '2020-01-01' AND '2021-01-01'
    GROUP BY fruit_code HAVING COUNT(DISTINCT county_code) = 1
)
SELECT c.county_name, COUNT(*) AS no_of_unique_fruits
  FROM fruit AS f
  JOIN county AS c
    ON f.county_code = c.county_code
   AND f.fruit_code IN (SELECT fruit_code FROM unique_fruits)
GROUP BY c.county_name
ORDER BY no_of_unique_fruits DESC

```

What have I forgotten here?