Mysql query grouping the results in the case

Asked

Viewed 144 times

2

I have the following appointment:

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 managed to get 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

Who 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 in single days,: 40,61,9,68,10 is from the whole month and not just from the days shown, what I’m doing wrong and what I could do to get it right ?

mysql

  • Dude, I think from what I understand, you should first use PIVOT instead of the case. Please study this link: http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

1 answer

3


I think the problem is Count, try it like this:

SUM(CASE DAY(o.data_fechamento) WHEN 22 THEN 1 ELSE 0 END) AS '22'

In Mysql this can work also, more elegant:

SUM(o.data_fechamento = 22) AS '22'

  • Your first answer solved my problem, already this returned empty: SUM(o.data_lock = 22) AS '22, Thanks!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.