I have the following query:
SELECT
DAY(o.data_fechamento) AS DIA,
u.nome AS TEC,
COUNT(*) AS TOTAL
FROM
ocorrencias AS o
LEFT JOIN sistemas AS a ON a.sis_id = o.sistema
LEFT JOIN localizacao AS l ON l.loc_id = o.local
LEFT JOIN instituicao AS i ON i.inst_cod = o.instituicao
LEFT JOIN usuarios AS u ON u.user_id = o.operador
LEFT JOIN usuarios AS ua ON ua.user_id = o.aberto_por
LEFT JOIN 'status' AS s ON s.stat_id = o.status
LEFT JOIN status_categ AS stc ON stc.stc_cod = s.stat_cat
LEFT JOIN problemas AS p ON p.prob_id = o.problema
LEFT JOIN sla_solucao AS sls ON sls.slas_cod = p.prob_sla
LEFT JOIN prioridades AS pr ON pr.prior_cod = l.loc_prior
LEFT JOIN sla_solucao AS slr ON slr.slas_cod = pr.prior_sla
LEFT JOIN script_solution AS sol ON sol.script_cod = o.oco_script_sol
LEFT JOIN prior_atend AS prioridade_atendimento ON prioridade_atendimento.pr_cod = o.oco_prior
WHERE
o.status IN (4)
AND MONTH( o.data_fechamento) = MONTH(NOW())
AND YEAR( o.data_fechamento) = YEAR(NOW())
AND u.nome NOT IN ('ADMINISTRADOR')
GROUP BY u.nome,DAY(o.data_fechamento)
It returns as follows:
DIA NOME TOTAL
24 MARCOS 1
3 MAURICIO 1
3 PEDRO 1
4 PEDRO 1
6 PEDRO 1
7 PEDRO 3
10 PEDRO 2
24 PEDRO 1
3 ALINE 1
4 ALINE 6
6 ALINE 5
7 ALINE 3
10 ALINE 2
12 ALINE 1
13 ALINE 4
18 ALINE 1
19 ALINE 3
24 ALINE 2
25 ALINE 3
26 ALINE 9
3 CARLOS 13
6 CARLOS 3
10 CARLOS 3
11 CARLOS 11
12 CARLOS 11
13 CARLOS 1
17 CARLOS 2
20 CARLOS 2
24 CARLOS 3
27 CARLOS 12
3 JOAQUIM 1
4 JOAQUIM 1
10 JOAQUIM 1
17 JOAQUIM 1
18 JOAQUIM 1
24 JOAQUIM 1
26 JOAQUIM 3
3 JOAO 12
4 JOAO 12
5 JOAO 1
6 JOAO 3
7 JOAO 4
10 JOAO 3
11 JOAO 4
12 JOAO 5
13 JOAO 4
19 JOAO 3
20 JOAO 5
24 JOAO 1
25 JOAO 2
26 JOAO 5
27 JOAO 4
3 DIEGO 2
6 DIEGO 2
12 DIEGO 6
I need it to return the days in lines and total below the days, I got to this query:
SELECT
u.nome AS TEC,
(CASE DAY(o.data_fechamento) WHEN 1 THEN COUNT(*) ELSE 0 END) AS '1',
(CASE DAY(o.data_fechamento) WHEN 2 THEN COUNT(*) ELSE 0 END) AS '2',
(CASE DAY(o.data_fechamento) WHEN 3 THEN COUNT(*) ELSE 0 END) AS '3',
(CASE DAY(o.data_fechamento) WHEN 4 THEN COUNT(*) ELSE 0 END) AS '4',
(CASE DAY(o.data_fechamento) WHEN 5 THEN COUNT(*) ELSE 0 END) AS '5',
(CASE DAY(o.data_fechamento) WHEN 6 THEN COUNT(*) ELSE 0 END) AS '6',
(CASE DAY(o.data_fechamento) WHEN 7 THEN COUNT(*) ELSE 0 END) AS '7',
(CASE DAY(o.data_fechamento) WHEN 8 THEN COUNT(*) ELSE 0 END) AS '8',
(CASE DAY(o.data_fechamento) WHEN 9 THEN COUNT(*) ELSE 0 END) AS '9',
(CASE DAY(o.data_fechamento) WHEN 10 THEN COUNT(*) ELSE 0 END) AS '10',
(CASE DAY(o.data_fechamento) WHEN 11 THEN COUNT(*) ELSE 0 END) AS '11',
(CASE DAY(o.data_fechamento) WHEN 12 THEN COUNT(*) ELSE 0 END) AS '12',
(CASE DAY(o.data_fechamento) WHEN 13 THEN COUNT(*) ELSE 0 END) AS '13',
(CASE DAY(o.data_fechamento) WHEN 14 THEN COUNT(*) ELSE 0 END) AS '14',
(CASE DAY(o.data_fechamento) WHEN 15 THEN COUNT(*) ELSE 0 END) AS '15',
(CASE DAY(o.data_fechamento) WHEN 16 THEN COUNT(*) ELSE 0 END) AS '16',
(CASE DAY(o.data_fechamento) WHEN 17 THEN COUNT(*) ELSE 0 END) AS '17',
(CASE DAY(o.data_fechamento) WHEN 18 THEN COUNT(*) ELSE 0 END) AS '18',
(CASE DAY(o.data_fechamento) WHEN 19 THEN COUNT(*) ELSE 0 END) AS '19',
(CASE DAY(o.data_fechamento) WHEN 20 THEN COUNT(*) ELSE 0 END) AS '20',
(CASE DAY(o.data_fechamento) WHEN 21 THEN COUNT(*) ELSE 0 END) AS '21',
(CASE DAY(o.data_fechamento) WHEN 22 THEN COUNT(*) ELSE 0 END) AS '22',
(CASE DAY(o.data_fechamento) WHEN 23 THEN COUNT(*) ELSE 0 END) AS '23',
(CASE DAY(o.data_fechamento) WHEN 24 THEN COUNT(*) ELSE 0 END) AS '24',
(CASE DAY(o.data_fechamento) WHEN 25 THEN COUNT(*) ELSE 0 END) AS '25',
(CASE DAY(o.data_fechamento) WHEN 26 THEN COUNT(*) ELSE 0 END) AS '26',
(CASE DAY(o.data_fechamento) WHEN 27 THEN COUNT(*) ELSE 0 END) AS '27',
(CASE DAY(o.data_fechamento) WHEN 28 THEN COUNT(*) ELSE 0 END) AS '28',
(CASE DAY(o.data_fechamento) WHEN 29 THEN COUNT(*) ELSE 0 END) AS '29',
(CASE DAY(o.data_fechamento) WHEN 30 THEN COUNT(*) ELSE 0 END) AS '30',
(CASE DAY(o.data_fechamento) WHEN 31 THEN COUNT(*) ELSE 0 END) AS '31'
FROM
ocorrencias AS o
LEFT JOIN sistemas AS a ON a.sis_id = o.sistema
LEFT JOIN localizacao AS l ON l.loc_id = o.local
LEFT JOIN instituicao AS i ON i.inst_cod = o.instituicao
LEFT JOIN usuarios AS u ON u.user_id = o.operador
LEFT JOIN usuarios AS ua ON ua.user_id = o.aberto_por
LEFT JOIN 'status' AS s ON s.stat_id = o.status
LEFT JOIN status_categ AS stc ON stc.stc_cod = s.stat_cat
LEFT JOIN problemas AS p ON p.prob_id = o.problema
LEFT JOIN sla_solucao AS sls ON sls.slas_cod = p.prob_sla
LEFT JOIN prioridades AS pr ON pr.prior_cod = l.loc_prior
LEFT JOIN sla_solucao AS slr ON slr.slas_cod = pr.prior_sla
LEFT JOIN script_solution AS sol ON sol.script_cod = o.oco_script_sol
LEFT JOIN prior_atend AS prioridade_atendimento ON prioridade_atendimento.pr_cod = o.oco_prior
WHERE
o.status IN (4)
AND MONTH( o.data_fechamento) = MONTH(NOW())
AND YEAR( o.data_fechamento) = YEAR(NOW())
AND u.nome NOT IN ('ADMINISTRADOR')
GROUP BY u.nome
Which returns me as follows:
MARCOS 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
PEDRO 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ALINE 0 0 0 0 0 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
CARLOS 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PAULO 0 0 40 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
JOAQUIM 0 0 61 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MAURICIO 0 0 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
EDER 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0
JOAO 0 0 0 0 0 0 0 0 0 0 0 68 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
SANDRO 0 0 0 0 0 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
LUCAS 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
DIEGO 0 0 0 0 0 0 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
It almost worked, but my results are accumulating on single days, these totals: 40,61,9,68,10 is the whole month and not just the days shown, what am I doing wrong and what could I do to get it right?