I have this data about brands and sellers.

[enter image description here]

select Brand, substring([Buy Box Seller],1,9) as BB_seller
from italy_keepa
where [Buy Box Seller] is not null
order by Brand

I want to transform it into this type of table – so actually I just need to count different types of sellers

I tried this code – but numbers in count are strange

select Brand, count(distinct([Buy Box Seller])) as BB_seller
from italy_keepa
group by Brand
order by BB_seller desc