Q:

Query count group by

I need a little help, I already checked other posts but don't understand how it works and how to make it work!
I have this query:
$sql = mysqli_query($conn,"SELECT count(id) as contador , Club_Name, estado FROM convidados  GROUP BY  Club_Name ORDER BY Club_Name ");

and the results are "fine", but I would like to add a counter to "estado = 1", so I can have clubname, contador(total per club) and total in (estado = 1)
When I do it at my own it only count one club and add 0 to all others...
Thanks!
Example
club name    total    total in
------------------------------
ABC            2          0
Banheiro      12          1
IlovePorto     8          0
Piolho         3          0

A:

you could use  case when (and sum)
    $sql = mysqli_query($conn,"SELECT count(id) as contador , 
            Club_Name, 
           sum( case when estado  = 1 then 1 else 0 end) tot_in 
           FROM convidados  GROUP BY  Club_Name ORDER BY Club_Name ");

