Selecting records for today - Part 2
This is in reference to my original post, which has been moved to PHP - I didn't realise until too late:
However this part is MySQL purely and NOT php.
ok guys. next part to the question, which is purely SQL not php related.
I want to count messages from user 8 where the recipient of the messages is unique between these date ranges - to see how many unique people they contacted.
2nd half- I would further like to refine the above, to only count the times the unque person replied - but this part is over my head.
if "user id: 1" contacts "user id: 2" 10 times and "user id: 2" replies ( once or more ) = 1 successful contact attempt
if "user id: 1" contacts "user id: 3" 20 times (or however many) and "user id: 3" doesnt reply = failed contact attempt
the table format is:
id (unique auto increment), from (user id of sender), to (user id of recipient), sent (time as int(10))
each message = 1 record
so if user 1 contacts user 2 = 1 record (from = 1, to = 2)
and user 2 replies to user 1 = 1 more record (from = 2, to = 1)
and user 2 messages user 3 = another record (from = 2, to = 3)
I really hope this makes sense.
So far I got:
SELECT count(DISTINCT messages.to) FROM messages WHERE messages.from = 8 and messages.sent >= 1258927200 and messages.sent <= 1259013599
But that only counts unique attempts (successful and failed contact attempts), not just successful attempts.