开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜