I am using the following query (thank you diafol) to group the top N rows in my data set:
SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, mgap_growth AS growth,mgap_recovery, (mgap_growth+mgap_recovery) total FROM (SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, mgap_growth, mgap_recovery,(mgap_growth+mgap_recovery) total, @acid_rank := IF(@current_acid = account_manager_id, @acid_rank + 1, 1) AS acid_rank, @current_acid := account_manager_id FROM mgap_orders ORDER BY account_manager_id, mgap_growth DESC ) ranked WHERE acid_rank <= 5
and the result is VERY close to what I need, but I am having an aggregate issue that I need help with. I have attcached a screenshot of my query results (I had to block out the customer names and ids for privacy; the mgap_ska_id and account_manager_id are INT columns and the mgap_ska_id_name is a VARCHAR.
In theory I need to SUM (I know its an aggregate; that is the issue) multiple mgap_growth values while keeping the ranking in tact. If I GROUP BY, then I lose the top 5 ranking. Currently, the mgap_growth value is only one value per mgap_ska_id within the mgap_growth column; I need it to be the SUM of all mgap_growth values per mgap_ska_id and keep the top five ranking as shown.