SQL SUM TOP 5 values per category then SUM category totals per ID

Question

I'm trying to produce an SQL statement to SUM top 5 values within categories per userID to create an overall total. Is this possible and how do I achieve it? I can sum the top 5 per single category or ALL but struggling to see how I can SUM each separate category total together.

For example,

ID           Cater   Weight
--------------------------------
1            Cheese    10
2            Bacon     15
1            Cheese    5
2            Bacon     10
1            Cheese    22
2            Cheese    5
1            Bacon     10
1            Cheese    10
2            Cheese    5
1            Cheese    20
2            Bacon     10
1            Cheese    30

The results i'm looking for is,

ID      Total_Weight
-------------------
1            102    Top 5 Cheese (10+22+10+20+30) + Top 5 Bacon (10)
2            45     Top 5 Cheese (5+5) + Top 5 Bacon (15+10+10)

Any values outside of the Top 5 are ignored.

The code below displays the SUM of the top 5 weights from ALL categories AS total weight. Can I achieve what I want from a single statement?

$log = "SELECT id, cater,
    SUM(weight) AS total_weight
   FROM   (   SELECT id,
                 CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1
                      ELSE @ROW_NUMBER := 1
                  END AS rn,
                 cater,
                 weight,
                 @id := id
            FROM individual,
                 (SELECT @ROW_NUMBER := 1, @ID := '') r
           ORDER
              BY
                 id, weight DESC
      ) TMP
 WHERE rn <= 5
   AND cater <> ''

   GROUP
BY id
 ORDER
BY total_weight DESC";

Answer 1

There might have some other better solution. But this will provide your expected result-

SELECT B.id, 
SUM(T_weight) Total_Weight,
group_concat(concat('TOP 5 ',B.cater,' (',B.T,')') SEPARATOR ' ') Details
FROM
(
    SELECT ID,cater,SUM(Weight) T_weight,group_concat(weight SEPARATOR '+') T
    FROM 
    (    
        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cheese' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Bacon' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat3' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat4' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat5' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat6' ORDER BY id, weight DESC
        )A WHERE rn < 6
    )A
    GROUP BY ID,Cater
)B
group by id

The output is-

1   191 TOP 5 Cheese (10+22+20+10+30) TOP 5 Cat3 (25+9+20+16+13) TOP 5 Bacon (10)
2   45  TOP 5 Cheese (5+5) TOP 5 Bacon (15+10+10)

Answer 2

If you have MySql version 8 or higher, you can use this code:

  SELECT id, SUM (weight)
    FROM (SELECT test.*,
                 ROW_NUMBER ()
                     OVER (PARTITION BY id, categ ORDER BY weight DESC)
                     rn
            FROM test) sub
   WHERE sub.rn < 6
GROUP BY id;

You need MySql 8+ because I used the function row_number and only from version 8 it exists. In the below fiddle example, I used Microsoft Sql Server 2017 because they didn't had MySql 8 or higher.

You can see your example here.