Mysql GROUP BY - PHP error

Question

I have a query in Mysql that, in the command line and in Mysql Workbench, is bringing the correct values, but when running it in PHP, the function GROUP BY is grouping more than it should!

Here is the query:

SELECT
    DATE_FORMAT(ctg.data_de_criacao, '%d/%m/%Y') AS DataMes,
    ctg.assunto,
    COUNT(*) AS InTS
FROM
    registro_atend AS ctg FORCE INDEX (idx_datacriacao)
    INNER JOIN
    registro_atend_ocorrencias AS cto ON ctg.idx_primary = cto.idx_primary
WHERE
    ctg.protocolo_da_ocorrencia > 0
    AND
                ctg.data_de_criacao BETWEEN '2016-01-01 00:00:00' AND '2016-12-31 23:59:59'
    AND
        ctg.campanha REGEXP 'camp1|camp2'
    AND
        ctg.assunto REGEXP 'card|credit|delivery'
    AND
        ctg.detalhe_do_assunto REGEXP 'analisys|not_delivered|receive_error|password_mail_corrupt'
    AND
        cto.setor_responsavel REGEXP 'credit1|delivery1'
GROUP BY DataMes, ctg.assunto
ORDER BY ctg.assunto, DataMes;

In Workbench, the query brings me 77 lines, usually. Already in PHP, only 9 are returned. (I used the var_dump after consultation)

I ran some tests, and the problem is in group by, but I need the result to come out in this format.

I have more than 100 queries in this same way, but only this is giving problem.

How to solve?

Answer 1

I switched the COUNT(*) for SUM(CASE WHEN ctg.assunto = 'xxxxx' THEN 1 END) and replaced the group with group by DataMes, so I have the counts of ctg.assunto in vertical columns, not in grouped rows.

It will do! But I still don’t understand why the previous one didn’t work, and all the others are working normally and the only thing that changes is the name of the columns...

You’ll understand!