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', 'Towels','Acme LLC', 52.01),
('Paper', 'Towels','Towel Direct', 40.43),
('Paper', 'Towels','Michigan Paper', 41.23),
('Paper', 'Towels','Cascades LLC', 40.55),
('Paper', 'Towels','Brawny', 26.33),
('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: 23 Duplicates: 0 Warnings: 0
SELECT category, subcategory,
CASE
WHEN grp <= 3 THEN MAX(vendor)
ELSE 'Others'
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 ROW_NUMBER() OVER (PARTITION BY category, subcategory ORDER BY SUM(amount) DESC)
ELSE 4
END AS grp
FROM purchases
GROUP BY category, subcategory, vendor
) T
GROUP BY category, subcategory, grp
ORDER BY category DESC, subcategory, grp
| category | subcategory | vendor | total_amount |
|---|---|---|---|
| Paper | Napkins | Paper LLC | 110.49 |
| Paper | Napkins | Dunder Mifflin | 70.99 |
| Paper | Napkins | Brawny | 60.02 |
| Paper | Napkins | Others | 73.49 |
| Paper | Towels | Acme LLC | 52.01 |
| Paper | Towels | Michigan Paper | 41.23 |
| Paper | Towels | Cascades LLC | 40.55 |
| Paper | Towels | Others | 66.76 |
| 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 |