Query mysql return 4 counts same table

Question

Query mysql return 4 counts same table

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I would like to do a select in the same table that contains 4 different values, all with clauses, I'm trying the following way but I'm not getting it, I want each count to be done in a STATUS where I specify there, but it is not returning me nothing, where am I 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 it to return for example:

Cidade  NEW  FUP  CE  COMPLETE
Campinas 19  11   5     100

mysql

asked by anonymous 20.02.2018 / 14:18

2 answers

3

You can use CASE with aggregate functions. this is an example that can work on most SGDBs:

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 the froma you need, for more information see the SUM case

20.02.2018 / 14:27

How to add css to all elements whose id contains "..."? java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob () has not yet been implemented

score 1 · Accepted Answer

The structure of CASE is slightly different in 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;