COUNT and GROUP BY in two columns

Question

COUNT and GROUP BY in two columns

Navigation

#1 by (7 votes)

4

Personal I am facing a question with a particular Query, I have already cataloged everywhere on the subject but without success. If there is another way to do what I'm trying, I'll thank you for the information

So, I'll illustrate the scenario.

TABLE VOUCHERS

InthistableIamassigningaserial_lotesincevoucherswillbegeneratedinlargequantities,eachvoucherhasanoverall_time.

Iwasabletocreateaquerywhereitreturnsalltheserial_lotethatwerecreatedinthelast2hoursandthetimesthatthislotrepeats.

SELECTserial_lote,Count(*)FROMvoucherswhere(data_criacaoBETWEENDATE_SUB(NOW(),INTERVAL2hour)ANDNOW())GROUPBYserial_loteHAVINGCount(*)>1orderbyserial_loteDESC

Ineedtogetbacknotonlytheserial_loteandthenumberoftimesthislotrepeats,butalsotheTotal_Timeofeachlot.(LotscanonlybecreatedwiththesameTotal_time)

php mysql sql

asked by anonymous 19.11.2017 / 08:12

1 answer

What encryption does Asp.net Identity use? Concatenation of two linked lists

score 7 · Accepted Answer

The SUM() function does not work with data of type Time . This is why you need to use the TIME_TO_SEC and SEC_TO_TIME functions to convert the time into 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 do not want to add the total time, just add the total_time in SELECT and 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