SQLite – Top 10, 20, 30 in Group Query

greatest-n-per-groupsqlite

Morning all! I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things.

One of the things I need to find is the total cost per team for the cheapest 10, 20, and 30 players. I've tried a number of suggestions I've seen online and none seem to work for me.

My database has the following fields:

Player | Overall | Sell | Buy | Series | Dash | Team | League | Division | Position | Pull_Date_Time

Here's what I currently use to get the total cost for each team:

select team, sum(buy) from Players 
where series = 'Live' and pull_date_time = '2019-04-09 08:46:04'
group by team
order by sum(buy)

I need the same data – just only selecting the cheapest 10, 20 and 30 players for each team and grouped by team like shown above. I do need to include the where clause that I'm currently using. I don't need this to be just 1 query – I fully expect that each count (10, 20, 30) will need it's own query.

Best Answer

Assuming that you have SQLite 3.25.0 or later, use a window function to compute the number of a row in each sorted team:

SELECT Team,
       sum(Buy)
FROM (SELECT Team,
             Buy,
             row_number() OVER (PARTITION BY Team
                                ORDER BY Buy     ) AS RowNum
      FROM Players)
WHERE RowNum <= 10
GROUP BY Team
ORDER BY sum(Buy);
Related Question