By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `purchases` (
`category` varchar(50) NOT NULL,
`subcategory` varchar(50) NOT NULL,
`vendor` varchar(120) NOT NULL,
`amount` decimal(18,2) DEFAULT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `purchases` (`category`, `subcategory`, `vendor`, `amount`) VALUES
('Paper', 'Napkins','Brawny', 20.01),
('Paper', 'Napkins','Paper Tiger', 20.43),
('Paper', 'Napkins','Paper LLC', 20.13),
('Paper', 'Napkins','Dunder Mifflin', 20.33),
('Paper', 'Napkins','Brawny', 40.01),
('Paper', 'Napkins','Paper Tiger', 30.43),
('Paper', 'Napkins','Paper LLC', 40.13),
('Paper', 'Napkins','Paper LLC', 50.23),
('Paper', 'Napkins','Dunder Mifflin', 20.33),
('Paper', 'Napkins','Dunder Mifflin', 30.33),
('Paper', 'Napkins','Staples', 22.63),
('Paper', 'Napkins','BrawnyII', 110.49),
('Food', 'Apples','Orchards LLC', 52.31),
('Food', 'Apples','Orchards LLC', 32.35),
('Food', 'Apples','Orchards LLC', 12.43),
('Food', 'Apples','Apples LLC', 40.43),
('Food', 'Apples','Northstar Orchard', 41.23),
('Food', 'Pizza','Pizza Hut', 10.25),
('Food', 'Pizza','Dominos', 16.53);
Records: 19 Duplicates: 0 Warnings: 0
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 dense_rank() 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
| category | subcategory | vendor | total_amount |
|---|---|---|---|
| Paper | Napkins | Paper LLC | 110.49 |
| Paper | Napkins | BrawnyII | 110.49 |
| Paper | Napkins | Dunder Mifflin | 70.99 |
| Paper | Napkins | Brawny | 60.02 |
| Paper | Napkins | 2 Others | 73.49 |
| Food | Apples | Orchards LLC | 97.09 |
| Food | Apples | Northstar Orchard | 41.23 |
| Food | Apples | Apples LLC | 40.43 |
| Food | Pizza | Dominos | 16.53 |
| Food | Pizza | Pizza Hut | 10.25 |