Question 112920 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Need to retrieve top 3 vendors by total amount, per category – subcategory

I have this db table: http://sqlfiddle.com/#!9/e1dd38e/7

I am trying to query it so it goes thru each category, returning each subcategory’s top 3 vendor’s, ordered by their total of spend (sum(amount)), the vendors outside of the top 3 will be summed up as a total and be included as "Others"

Also including total amount for category and subcategories

I appreciate any help, struggling on this one

Expected outcome would be:

Paper 515.54
    Napkins 314.99
        Paper LLC 110.49
        Dunder Mifflin 70.99
        Brawny 60.02
        Others 73.49
    Towels 200.55
        Acme LLC 52.01
        Michigan Paper 41.23
        Cascades LLC 40.55
        Others 66.76
Food 205.54
    Apples 178.76
        Orchards LLC 97.10
        Northstar Orchard 41.23
        Apples LLC 40.43
    Pizza 26.78
        Dominos 16.53
        Pizza Hut 10.25
Tags:

2

Answers


  1. 0

    You may use two levels of aggregation with the assistance of ROW_NUMBER() function to define unique groups for the other vendors whose their sum of amount is ranked 4 or more as the following:

    SELECT category, subcategory, 
       CASE grp WHEN 'Others'
         THEN CONCAT(COUNT(*),' ', grp)
         ELSE grp
       END AS vendor,
       SUM(sum_amount) AS total_amount
FROM
(
  SELECT category, subcategory, vendor, SUM(amount) sum_amount,
       CASE 
         WHEN ROW_NUMBER() OVER (PARTITION BY category, subcategory ORDER BY SUM(amount) DESC) <=3
         THEN vendor
         ELSE 'Others'
       END AS grp
  FROM purchases
  GROUP BY category, subcategory, vendor
) T
GROUP BY category, subcategory, grp
ORDER BY category DESC, subcategory, 
         CASE WHEN grp<>'Others'THEN 1 ELSE 2 END, 
         SUM(sum_amount) DESC

    See a demo.

    In case that there could be more than one vendor with the same top three sum of amount value and you want to list them all (not only three of them) then replace the ROW_NUMBER() function with DENSE_RANK() function . See a demo on a modified dataset.

    Login or Signup to reply.
  2. 0

    For the new requested requirement, Also including total amount for category and subcategories, you may use a sum window function within the provided query in the previous answer as the following:

    WITH purchases_cat_sum AS
(
  SELECT *,
         SUM(amount) OVER (PARTITION BY category) category_total_amount,
         SUM(amount) OVER (PARTITION BY category, subcategory) subcategory_total_amount
  FROM purchases
)
SELECT category, category_total_amount,
       subcategory, subcategory_total_amount,
       CASE grp WHEN 'Others'
         THEN CONCAT(COUNT(*),' ', grp)
         ELSE grp
       END AS vendor,
       SUM(sum_amount) AS total_amount
FROM
(
  SELECT category, category_total_amount,
         subcategory, subcategory_total_amount,
         vendor, SUM(amount) sum_amount,
       CASE 
         WHEN ROW_NUMBER() OVER (PARTITION BY category, subcategory ORDER BY SUM(amount) DESC) <=3
         THEN vendor
         ELSE 'Others'
       END AS grp
  FROM purchases_cat_sum
  GROUP BY category,category_total_amount, 
           subcategory, subcategory_total_amount, 
           vendor
) T
GROUP BY category,category_total_amount, 
         subcategory, subcategory_total_amount, 
         grp
ORDER BY category DESC, subcategory, 
         CASE WHEN grp<>'Others'THEN 1 ELSE 2 END, 
         SUM(sum_amount) DESC

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search