How to recover data from a different Where SUM?

Asked

Viewed 34 times

1

I have a work point timetable, which has the data_inicio, data_fim, usuario_id and extra indicating 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 worked regular and overtime (extra = 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 several ways already it seems that there is no solution without the query has to be rewritten within a subquery.

Is there any way:

SUM(TIMESTAMPDIFF(SECOND, tb.data_inicio, tb.data_fim)) AS total_segundos_regulares, receive some parameter that discriminates tb.extra = 0 and one other:

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

mysql

1 answer

3


You can add with IF within their 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 fields extra and sum in two separate aliases.

Browser other questions tagged mysql

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