Query mysql return 4 Counts same table

Question

I would like to make a select in the same table that counts 4 different values, all with clauses, I’m trying this way but I’m not getting it, I want each Count to be done in a STATUS where I specify there, but it’s not returning me anything, where I’m going wrong?

SELECT count(id) as new AND status IN ('pgto_confirmado', 'pending'), count(id) as fup AND status IN ('processing'), count(id) as ce AND status IN('confirmar_entrega'), count(id) as complete AND status IN ('complete', 'repassado') 
FROM sgn_tbd_pedido 
WHERE data_entregar LIKE '%2018-03-08%' 
GROUP BY cidade;

I would like you to return for example:

Cidade  NEW  FUP  CE  COMPLETE
Campinas 19  11   5     100

Answer 1

you can use CASE with aggregated functions. this is an example that can work in most DBMS:

select campo_id,
    count(*) total,
    sum(case when algumCampo = 'valoresperado' then 1 else 0 end) toal1,
    sum(case when algumCampo = 'outrovalor' then 1 else 0 end) toal12,
from suaTabela
group by cidade

There you can customize from the froma you find necessary, for more information see the SUM case

Answer 2

The structure of CASE is a little different in the mysql; try the code below:

SELECT 
    sum (CASE status WHEN 'pgto_confirmado' THEN 1 WHEN 'pending' THEN 1 ELSE 0 END) as new
    sum (CASE status WHEN 'processing' THEN 1 ELSE 0 END) as fup,
    sum (CASE status WHEN 'confirmar_entrega' THEN 1 ELSE 0 END) as ce,
    sum (CASE status WHEN 'complete' THEN 1 WHEN 'repassado' THEN 1 ELSE 0 END) as complete
FROM sgn_tbd_pedido 
WHERE data_entregar LIKE '%2018-03-08%' 
GROUP BY cidade;