SQL - Get answer from query as a single number

Question

The following code returns a couple of numbers, identifying people who take part in more than three activities.

SELECT pnr
FROM Participates
GROUP BY pnr
HAVING count(activities)>3;

I want the answer to be the number of people who participate in more than three activities though, i.e. "4", instead of four unique numbers. What to do?

Answer 1

You will need a WHERE clause on the pnr field to uniquely identify one of your groupings:

SELECT COUNT(pnr)
FROM Participates
GROUP BY pnr
WHERE pnr = 'whatever'
HAVING COUNT(activities)>3

The order of my clauses might be wrong

Answer 2

Select Count(Distinct pnr)
From Participates
Having Count(activities) > 3

Answer 3

Access supports derived tables.

SELECT COUNT(*) AS NumberOfParticipants FROM 
(
SELECT pnr
  FROM Participates
 GROUP BY pnr
HAVING count(activities)>3
) T