The point is that nobody knows what we're looking for and how to find it. These are just my guesses.
I have no idea what your program is suppose to be doing as a final result or how this should be presented to the user, since you are the programmer you are the one in control. So, yes unless you do not know what you wish to achieve/accomplish in the end then we do not know either
This can be compared to "looking for rain drops that will fall on three flowers in a large garden at the same time. If this event occurs again within a few minutes (at least once), there is a suspicion of success."
The answer to that question is 42 btw.
If the value in the DUP column is, for example, "2", we still do not know which Device_ID make up it. There would have to be an additional column in which it will show Devices numbers ... See screen in the attachment
Yeah, and that is impossible to realise because the dupcount is/can be made up of multiple Device_ID's.
Come to think about it, why do you need a duplicate-count to begin with ? imho it isn't helpful at all to know the number of duplicates, unless you have a specific purpose for it ? (which is currently unknown to us, or at least to me).
afaik this is how you manage to create a list of unique duplicates (assuming data is the name of the SQL table):
SELECT DISTINCT t1.datetime, t1.device_id FROM DATA AS t1 INNER JOIN DATA AS t2 ON t1.datetime = t2.datetime WHERE t1.device_id <> t2.device_id ORDER BY t1.datetime, CAST(t1.device_id AS INTEGER);
And, again afaik, this creates a list of the duplicate items.
SELECT t1.datetime, t1.device_id, t1.user_id FROM DATA AS t1 INNER JOIN (SELECT datetime, device_id, COUNT(*) AS dupcount FROM DATA GROUP BY datetime, device_id HAVING dupcount > 1) AS t2 ON t1.datetime = t2.datetime AND t1.device_id = t2.device_id ORDER BY t1.datetime, t1.device_id;
Both show the device_id's that have a duplicate datetime field.
edit: and that picture ... is exactly the kind of cascading that makes those rays act in chaos.... you will never see those figures, at least not by the provided raindrops