1.) make a numbers table
CREATE TABLE IF NOT EXISTS `numbers` (
`N` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `numbers` (`N`) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
2.)
select
Date, Gross, Fee, Net, Transaction_ID,
substring_index(
substring_index(Item_ID, ',', n),
',',
-1
) as Item_ID, Country
from paypal
join numbers
on char_length(Item_ID)
- char_length(replace(Item_ID, ',', ''))
>= n - 1
3.) to find the count
SELECT Item_ID, COUNT(Item_ID) as count FROM paypal
GROUP BY Item_ID
ORDER BY count DESC