Benjamin_11 0 Newbie Poster

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.

b6a55850da4d8e9bc3ad4b2616d4b3c3

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.

Ready;set;go!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.