TOP 5 AND SUM EVERYTHING ELSE

[Guest]

OK this is what I need:
Need a query to select top 5 from a list and to sum every other result as
"Others", basicaly I need the query to return SIX results.

Table "Scores":
field1 field2
12 Ann
9 Marc
5 Carl
3 Lisa
2 Jules
1 ...
1 ...
1 ...
1 ...
1 ...

Query should return:

field1 field2
12 Ann
9 Marc
5 Carl
3 Lisa
2 Jules
5 Others

Thanks for the help.

 

[Guest]

Mybe there is a better way, but you can achive that using three queries

1. Return the TOP 5
Select TOP 5 Field2, Field1 From TableName Order By Field1 Desc

2. Return The rest
Select "Oters" As Field3 , Sum([Field1]) As Field4 From TableName
Where Field2 Not In (Select TOP 5 Field2 From TableName Order By Field1 Desc)

3. Combine both queries using a union query
Select Field2 , Field1 From Query1
UNION
Select Field3 , Field4 From Query2

 

[Michel Walsh]

Rank the result, GROUP BY on an appropriate transformation:



SELECT SUM(field1), LAST(iif( rank <= 5, field2, "---Others"))
FROM scores
GROUP BY iif( rank <= 5, rank, 6)




If the rank is not available to you, you have to compute it, one solution,
using join, is:


SELECT a.field1, a.field2, COUNT(*) AS rank
FROM scores AS a INNER JOIN scores AS b
ON a.field1< b.field1 OR ( a.field1=b.field1 AND a.field2<=b.field2)
GROUP BY a.field1, a.field2





You can remove the " OR ( a.field1=b.field1 AND a.field2<=b.field2) " if
you are sure field1 would never have ex-equo for field1. Note, that in case
of ex-equo, in position 5, the one with the smallest 'field2' value will be
picked. That may be unfair, for the other ex-equo, so, maybe:


SELECT a.field1, a.field2, COUNT(*) AS rank
FROM scores AS a INNER JOIN scores AS b
ON a.field1<= b.field1
GROUP BY a.field1, a.field2


is more appropriate, but you may end up have less than 5 records; with
values of : 12, 8, 7, 7, 7, 7, 5.. they are now ranked as 1, 2, 6, 6,
6, 6, 7, .... and the first query will return only 3 records (for the value
of 12, the value of 7, and sum of all the others)





Hoping it may help
Vanderghast, Access MVP