What i want to do is :
example db
id name
----- ------
1 Mark
2 Mike
3 Paul
4 Mike
5 Mike
6 John
7 Markexpected result
name count
----- -----
Mike 3
Mark 2
Paul 1
John 1
select user_id,
sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
from your_table
group by user_id
select name, count(*) as num
from your_table
group by name
order by count(*) desc