Search code examples
sqlgoogle-sheetsgoogle-query-language

Google Spreadsheet QUERY()

I use the following query to get the Top 5 items from a table in Google Spreadsheets. I would like to revise my query to get the Top 5 items AND the sum of the rest of the items in the table not part of the Top 5. eg: 10 rows total, Top 5 are separate rows and sum of 6-10 are one separate row named Other Items. I know how to do it using tSQL but it does not work in the subset of Google Query. Any help from someone familiar with the Google subset would be appreciated.

Thanks!

*Also I would be willing to have 2 separate queries but ideally it should be 1 single query.

=ARRAYFORMULA(QUERY($A$2:$G$24, "select A, G order by G desc limit 5"))

Solution

  • This formula might work as you want:

    ={QUERY($A$2:$G$24, "select A, G order by G desc limit 5");"Other Items",SUM(QUERY($A$2:$G$24, "select G order by G limit "&COUNTA($A2:$A24)-5))}