By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
category varchar(255) NOT NULL
);
INSERT INTO
products(category)
VALUES
('A'),
('B'),
('B'),
('C'),
('A'),
('D'),
('C'),
('D'),
('D'),
('C'),
('C'),
('E'),
('E'),
('A'),
('F'),
('D'),
('D'),
('G'),
('H'),
('G'),
('H'),
('G');
select
case when rn <= 3 then real_category else 'Other' end final_category,
sum(no_products) no_products
from (
select
coalesce(x.new_category, p.category) real_category,
count(*) no_products,
rank() over(order by count(*) desc) rn
from products p
left join (
select 'A' category, 'A-G' new_category
union all select 'G', 'A-G'
union all select 'B', 'B-C'
union all select 'C', 'B-C'
union all select 'E', 'E-F'
union all select 'F', 'E-F'
) x on x.category = p.category
group by real_category
) t
group by final_category
order by no_products desc
| final_category | no_products |
|---|---|
| A-G | 6 |
| B-C | 6 |
| D | 5 |
| Other | 5 |