Query count group by

Question

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

score 0 · Accepted Answer · answered Jun 22 '17 at 16:52

0

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 ");

answered Jun 22 '17 at 16:52

ScaisEdge

131,976
10
91
107

thanks! it works like i needed! but, can yo explain me better how it works? "sum( case when estado = 1 then 1 else 0 end) tot_in " it's lke an if state in querry?: if estado is 1 than add 1 else add 0? – Ussagui Jun 22 '17 at 19:27

Query count group by

1 Answers1