mysql - Conditionals and counts in SQL -

i have mysql table "ratings", with

   id column    column called like_dislike (holds either null, 0, or 1),    , column called lesson_id (a foreign key lessons).  

the mysql table, "lessons", with

   id column    teacher_id column 

i need select information:

   "select r.like_dislike ratings r inner join lessons l on l.lesson_id = r.lesson_id"; 

however, part of larger sql statement, , is:

   foreach lesson_id, if like_dislike == 0, select count(like_dislike) 

and

   foreach lesson_id, if like_dislike == 1, select count(like_dislike) dislike 

i not know how turn pseudo code sql. need in sql, rather in php, because part of larger sql statement conversion formatted arrays troubling.

you should able accomplish grouping. example:

select r.lesson_id, count(*) ratings r inner join lessons l on l.lesson_id = r.lesson_id r.like_dislike = 0 group r.lesson_id; 

the same dislike, change where clause

where r.like_dislike = 1 

edit:

this can combined 1 query requested, adding level of grouping:

select r.lesson_id, r.like_dislike, count(*) count ratings r inner join lessons l on l.lesson_id = r.lesson_id group r.lesson_id, r.like_dislike; 

this give output, example:     +-----------+--------------+-------+     | lesson_id | like_dislike | count |     +-----------+--------------+-------+     |         1 |            0 |    12 |     |         1 |            1 |     7 |     |         2 |            0 |     1 |     |         2 |            1 |     4 |     +-----------+--------------+-------+

so lesson_id of 1, there 12 likes, , 7 dislikes, etc...

edit 2:

to 1 row each lesson_id, can modify statement little:

select r.lesson_id,   case when r.like_dislike = 0 count(*) end like,   case when r.like_dislike = 1 count(*) end dislike ratings r inner join lessons l on l.lesson_id = r.lesson_id group r.lesson_id, r.like_dislike; 

for matter, don't need join on lessons table @ all, unless somehow getting ratings not link lesson. if want include lessons have no ratings, have change outer join:

select l.lesson_id,   case when r.like_dislike = 0 count(*) end like,   case when r.like_dislike = 1 count(*) end dislike lessons l left join ratings r on r.lesson_id = l.lesson_id group l.lesson_id, r.like_dislike;