database - SQL SELECT Query with group and distinct -

i have table 4 columns (id, bm, name, act).

i want retrieve records grouped bm , count how many records have of every group act = no , act = yes @ once...

so if have records:

(id, bm, name, act) 1, 5, nik, yes 2, 6, mike, yes 3, 5, tom, no 4, 5, alex, no

result need is:

(bm, totalyes, totalno) 5, 1, 2 6, 1, 0

i guess possible retrieve sql don't know how :(

you can use conditional aggregation achieve result:

select    bm,    sum(case when act = 'yes' 1 else 0 end) "count of yes",   sum(case when act = 'no'  1 else 0 end) "count of no" t group bm;

with databases, mysql, can reduce aggregation sum(act = 'yes'), ansi sql standard requires full case expression.

Two