I need to count all the rows in a MySQL table omitting the records with same agent
and msisnd
columns and dates closer than 5 minutes to each other.
For example,
id type agent msisdn date
2706 jurnal EC_Catalin.Silvan 745844472 2011-02-18 18:31:54
63535 item EC_Adelina.Gartan 766699747 2011-02-22 18:42:15
56513 item EC_Adelina.Gartan 766733667 2011-02-24 17:13:31
56372 item EC_Adelina.Gartan 766733667 2011-02-24 17:12:01
The query should return COUNT = 3 because the rows with id's 56513 and 56372 have close dates to each other.
So far i've got this query and it's not working
SELECT `calls_count_pre`.*
FROM `calls_count_pre`
LEFT JOIN `calls_count_pre` AS `temp` ON (`calls_count_pre`.`id` = `temp`.`id`)
WHERE
(MINUTE(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) <= 5 AND HOUR(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) 开发者_C百科= 0)
AND `calls_count_pre`.`msisdn` = `temp`.`msisdn`
AND `calls_count_pre`.`agent` = `temp`.`agent`
ORDER BY `calls_count_pre`.`agent`, `calls_count_pre`.`msisdn`, `calls_count_pre`.`date` DESC
It's simply returning all records. I know I'm not selecting a COUNT, but even a SELECT *
would be a start at this point.
Thanks in advance.
You're on the right track with a self-join, but I think you might be joining on the wrong column. And I think you're going to need a GROUP BY
as well.
Try with something more like this:
SELECT calls_count_pre.*
FROM calls_count_pre
LEFT JOIN calls_count_pre AS temp
ON calls_count_pre.msisdn = temp.msisdm AND calls_count_pre.agent = temp.agent
AND temp.date > calls_count_pre.date
AND DATESUB(temp.date, INTERVAL 5 MINUTE) < calls_count_pre.date
GROUP BY calls_count_pre.agent
精彩评论