Count totals for each group/set by how many occurrences they appea

[Guest]

I have a query that lists the results of a quiz. The key field here is
'Run_No'. This represents a group or session.

I want to to summarise the top 10 occurrences of 'Run_No' denoted by how
many times each Run_No occurs in the example table below.

Run_No Quiz_ID score answer_box
8 23367 1 Yes
4 23367 1 Yes
2 23367 1 Yes
3 23369 1 Yes
3 23372 1 Yes
1 23373 1 Yes
1 23374 0 No
7 23376 0 No
19 23376 1 Yes
2 23381 0 No
11 23384 1 Yes
2 23386 1 Yes
10 23386 1 Yes
2 23390 1 Yes
19 23392 1 Yes
7 23392 0 No
11 23395 0 No


My results query/table should look like this:

Run_No Occurs
1 2
2 4
3 2
4 1
7 2
8 1
10 1
11 2
19 2

 

[Guest]

If you want them listed by the number of occurences, decreasing, try:

SELECT TOP 10 Run_No, COUNT(Run_No) as Occurs
FROM yourTable
GROUP BY Run_No
ORDER BY COUNT(Run_No) DESC

If you want them listed in increaseing Run_No order, as depicted, try:

SELECT Run_No, Occurs
FROM (
SELECT TOP 10 Run_No, COUNT(Run_No) as Occurs
FROM yourTable
GROUP BY Run_No
ORDER BY COUNT(Run_No) DESC
) as Top10
ORDER BY Run_No

HTH
Dale

 

[Guest]

Thanks Dan,

It worked a treat. Another real world lesson where I have learned something
about queries.