CODEWITHSUNDEEP
sqlgroup-bysql-order-by
phoxley
phoxley

Reputation: 474

SQL Get top 3 entries for each group

I have a table that contains a dataset similar to the one below. There are thousands of entries that have a group id and an amount.

+-------+--------+
| Group | amount |
+-------+--------+
| 1     | 10     |
| 1     | 15     |
| 1     | 11     |
| 1     | 5      |
| 1     | 100    |
| 2     | 200    |
| 2     | 8      |
| 2     | 12     |
| 2     | 23     |
| 2     | 40     |
| 2     | 40     |
| 2     | 40     |
| 3     | 3      |
| 3     | 3      |
+-------+--------+

For each group, I need the (rounded) average of the top three entries with the highest amount within the group. If there are less than three entries for the group, the average is based on the available entries.

The potential table could look like, e.g. group #1: (100 + 15 + 11)/3 = 42 :

+-------+--------+
| Group | amount |
+-------+--------+
| 1     | 42     |
| 2     | 92     |
| 3     | 3      |
+-------+--------+

There can be 1 or more entries for each group and the amounts are positive integers but they can have the same value.

I've played around with Group by and order by but it don't seem to get it right. Any help is appreciated. Thanks.

Upvotes: 0

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Most databases support the ANSI standard row_number() function, which can help you solve this problem. Something like this will work in most databases:

select `group`, cast(0.5 + avg(amount) as int) as RoundedAverage
from (select t.*,
             row_number() over (group by "group" order by amount desc) as seqnum
      from t
     ) t
where seqnum <= 3
group by `group`;

If you don't have row_number() in your database, you can do the work with a subquery or some other way, although an id column is useful for this purpose.

Upvotes: 1

Related Questions