I've researched most of the time with this topic, however I couldn't get a efficient and perfect answer regarding ranking (top 3) a MySQL table with group and aggregate using sum() to the rest.
The data are as following:
TS | Name | Count
=============================
1552286160 | Apple | 7
1552286160 | Orange | 8
1552286160 | Grape | 8
1552286160 | Pear | 9
1552286160 | Kiwi | 10
...
1552286100 | Apple | 10
1552286100 | Orange | 12
1552286100 | Grape | 14
1552286100 | Pear | 16
1552286100 | Kiwi | 9
...
1552286040 | Apple | 4
1552286040 | Orange | 2
1552286040 | Grape | 3
1552286040 | Pear | 7
1552286040 | Kiwi | 9
...
With this dataset, I would like to form Top 3 by each TS group, and 1 row with sum(Count) of the rest that group, like following:
TS | Name | Count
=============================
1552286160 | Kiwi | 10
1552286160 | Pear | 9
1552286160 | Grape | 8
1552286160 | Other | 8 + 7
...
1552286100 | Pear | 16
1552286100 | Grape | 14
1552286100 | Orange | 12
1552286100 | Other | 10 + 9
...
1552286040 | Kiwi | 9
1552286040 | Pear | 7
1552286040 | Apple | 4
1552286040 | Other | 3 + 2
...
The closest hint is actually provided via http://www.silota.com/docs/recipes/sql-top-n-aggregate-rest-other.html However, the solution was just for a single group.
The SQL Fiddle that I've prepared is located here: http://sqlfiddle.com/#!9/3cedd0/10
Appreciate if there's any solutions.