SQL - Get answer from query as a single number
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 number开发者_JAVA技巧s. What to do?
Access supports derived tables.
SELECT COUNT(*) AS NumberOfParticipants FROM
(
SELECT pnr
FROM Participates
GROUP BY pnr
HAVING count(activities)>3
) T
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
Select Count(Distinct pnr)
From Participates
Having Count(activities) > 3
精彩评论