SELECT COUNT(DISTINCT MAINSERNO) AS TOTAL
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 150
SELECT DISTINCT MAINSERNO, to_char(TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') as TIMESTAMP
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 165, of which 15 are MAINSERNO are duplicates. I have used the DISTINCT, why does it still produces duplicates?
Also, based on its TIMESTAMP, I would want to extract the count, and put them into its appropriate hourly time slot as shown in the printscreen, is that possible?
That is e.g from TIMESTAMP data of HH:MM:SS, those data that are after 00:30:00 and before 01:30:00 would be counted and place into the 01:30 Hours(18) timeslot.
How do I loop through the 24-hour time slot, instead of repeating each one as below:
also possible not to include the date, and only the time in the comparison condition?
