How to retrieve data from a SUM with where different?

Question

How to retrieve data from a SUM with where different?

Navigation

#1 by (3 votes)

1

I have a working time table, which has the data_inicio , data_fim , usuario_id and extra that indicates whether it is overtime or not.

I already have a query that brings the sum in seconds worked per year, month, user of the last 6 months.

But now I also need to bring the sum of second regular and overtime work (% with% = 1).

SELECT 
        us.nome as usuario, 
        MONTH(tb.data_inicio) AS mes,
        YEAR(tb.data_inicio) AS ano,
        SUM(TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim)) AS total_segundos,
        COUNT(DISTINCT DAY(tb.data_inicio)) AS total_dias

FROM 'horario_ponto' tb 

INNER JOIN 'usuario' us ON us.id = tb.usuario_id 

WHERE tb.data_inicio > (LAST_DAY(NOW() - INTERVAL 6 MONTH) + INTERVAL 1 DAY)

GROUP BY usuario, ano, mes
ORDER BY ano DESC, mes DESC, usuario ASC

I have tried in many ways already, there seems to be no solution without the query having to be rewritten inside a subquery.

Is there any way to:

extra receive some parameter that discriminates SUM(TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim)) AS total_segundos_regulares, and in another:

tb.extra = 0 receive SUM(TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim)) AS total_segundos_extra ?

mysql

asked by anonymous 07.05.2018 / 22:54

1 answer

Calling Jquery function inside a For loop How to get the value of some select item when clicking on it

score 3 · Accepted Answer

You can sum with IF within your SUM'S .

SELECT  
        us.nome as usuario, 
        MONTH(tb.data_inicio) AS mes,
        YEAR(tb.data_inicio) AS ano,
        SUM(IF(extra = 0,TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim),0)) AS total_segundos_regulares,
        SUM(IF(extra = 1,TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim),0)) AS total_segundos_extras,
        COUNT(DISTINCT DAY(tb.data_inicio)) AS total_dias
FROM 'horario_ponto' tb 
INNER JOIN 'usuario' us ON us.id = tb.usuario_id 
WHERE tb.data_inicio > (LAST_DAY(NOW() - INTERVAL 6 MONTH) + INTERVAL 1 DAY)
GROUP BY usuario, ano, mes
ORDER BY ano DESC, mes DESC, usuario ASC

Checks the extra and sum fields in two separate aliases.