mysql - Count rows with certain condition and still show all rows -

my table looks this:

col1      col2 denmark   yes denmark   no denmark   yes brazil    yes brazil    no brazil    no

i want count distinct countries , tally number of yes in separate column. example:

col3      col4 denmark   2 brazil    1

what best way count 'yes' , show distinct countries col1?

try this:

select col1 col3, sum(case when col2 = 'yes' 1 else 0 end) col4 yourtable group col1

Call