COUNT and GROUP BY in two columns

Question

Personal I am facing a doubt with a particular query, I have searched everywhere on the subject but without success. If there’s any other way to do what I’m trying, I’d appreciate the information

So I’ll illustrate the scenario.

VOUCHER TABLE

In this table I am assigning a serial_lot since the vouchers will be generated in large quantities, each voucher has a Total Time_total.

I managed to create a query where returns me all serial_batch that were created in the last 2 hours and the times that this batch repeats.

SELECT serial_lote, Count(*) FROM vouchers
where (data_criacao BETWEEN DATE_SUB(NOW(), INTERVAL 2 hour) AND NOW())  GROUP BY serial_lote
HAVING Count(*) > 1 
order by serial_lote DESC

I need you to return not only the serial lot and the amount of times this batch repeats, but also the total Time_total of each batch. (Lots can only be created with the same Total Time)

Answer 1

The function SUM() does not work with type data Time. That’s why you have to use the functions TIME_TO_SEC and SEC_TO_TIME to convert time to a number of seconds and vice versa.

SELECT serial_lote, Count(*), SEC_TO_TIME( SUM( TIME_TO_SEC( `tempo_total` ) ) ) AS tempoTotal
FROM vouchers
WHERE (data_criacao BETWEEN DATE_SUB(NOW(), INTERVAL 2 hour) AND NOW())  GROUP BY serial_lote
HAVING Count(*) > 1 
ORDER BY serial_lote DESC

EDIT: If you don’t want to add the total time, just add the total time in the SELECT and in the GROUP BY

SELECT serial_lote, tempo_total, Count(*)
FROM vouchers
WHERE(data_criacao BETWEEN DATE_SUB(NOW(), INTERVAL 2 hour) AND NOW())  
GROUP BY serial_lote, tempo_total
HAVING Count(*) > 1 
ORDER BY serial_lote DESC