Search code examples
google-query-language

Is it possible to select top 10 items and put the rest of the items in others? Just like we can do in SQL

Is it possible to create a google query to select top 10 items and group the rest in others category just like we can do with SQL. I am trying to pull the data from Google Spreadsheet.

Solution

  • This an example just to get you started with the Google sheets queering

    1. You have to separate top 10 from the group by part. Do a separate sheet take all records except top 10
    =QUERY(Hours!A2:BC18, "select A, B order by A offset 10")
    1. Create result sheet. In the first cell paste this query, this will give you top 10 rows
    =QUERY(Hours!A2:BC18, "select A order by A limit 10")

    Next in the cell in row 11, paste this query (ExcludingTop10 is sheet created in step 1)

    =QUERY(ExcludingTop10!A1:B7, "select MAX(A) GROUP BY B")

    The output would be similar to the following

    1148002773
1148003048
1148003741
1148005962
1148006591
1148007576
1148007609
1148039351
1148050401
1148050580
max <- you can hide this cell as well
1148079677

    You have 2 sheets one with all data and the other with summary.