Mysql – Selecting Consective rows with same Value & calcute the difference between timestamps of same value rows.Please Check What will b error in this query

MySQL

I have a table in which there are two columns
A_MSISDNs and Incoming_timestamp which is the time of call.

I want to calculate the time duration between two calls of same MSISDN
e.g:- 

A_MSISDNs        Incoming_timestamp               
420000128321     2018-06-26 06:26:02    
420000128321     2018-06-26 07:04:44

The Time Duration between Calls by above Same MSISDN is 28 minutes.

For this purpose I made query which is giving error 1064

Kindly correct my query if there is any syntax error and logic error 


SELECT MSISDN,total_count,TIMESTAMPDIFF(MINUTE, MIN(times), MAX(times)) AS duration

FROM (

SELECT t1.*
, @groupNumber := IF(@prev_MSISDN != t1.MSISDN, @groupNumber + 1, @groupNumber) AS gn
, @prev_msisdn := t1.MSISDN
FROM (

(#t1
#[ itwill returns the msisdn,incoming_timestamp of all msisdn and total count and order them according to their msisdn ,timestamp. We do this beacuse we want to retrive the different timestamp of calls of the same MSISDN 
SELECT tb1.a_msisdn AS 'MSISDN',tb1.incoming_timestamp AS 'Times',tb2.total_count AS 'Total_counts'
FROM cc_info  AS tb1
JOIN 
#{it will count the msisdn whose total_count>=10
(SELECT COUNT(1) AS 'Total_count',a_msisdn
FROM cc_info  
GROUP BY a_msisdn 
HAVING Total_count>=10 
ORDER BY incoming_timestamp) AS tb2
#}
ON tb1.`a_msisdn`=tb2.a_msisdn
WHERE tb1.a_msisdn=420000128321 #it is for testing
ORDER BY tb1.a_msisdn ,tb1.incoming_timestamp 
#]
) AS t1
,(SELECT @groupNumber := 0, @prev_MSISDN := NULL) var_init_subquerysa ORDER BY MSISDN,times 

) AS t2
)
GROUP BY gn,MSISDN

Best Answer

Seems to me that the cause of the syntax errors were extra brackets in lines 10 and 30, and a misspelled Total_counts in line 1.

Here is the script which compiles without syntax errors.
You can see a working SQL Fiddle script here.

Corrected script: 

SELECT MSISDN,Total_counts,TIMESTAMPDIFF(MINUTE, MIN(times), MAX(times)) AS duration

FROM (

SELECT t1.*
, @groupNumber := IF(@prev_MSISDN != t1.MSISDN, @groupNumber + 1, @groupNumber) AS gn
, @prev_msisdn := t1.MSISDN
FROM (

#t1
#[ itwill returns the msisdn,incoming_timestamp of all msisdn and total count and order them according to their msisdn ,timestamp
SELECT tb1.a_msisdn AS 'MSISDN',tb1.incoming_timestamp AS 'Times',tb2.total_count AS 'Total_counts'
FROM cc_info  AS tb1
JOIN 
#{it will count the msisdn whose total_count>=10
(SELECT COUNT(1) AS 'Total_count',a_msisdn
FROM cc_info  
GROUP BY a_msisdn 
HAVING Total_count>=10 
ORDER BY incoming_timestamp) AS tb2
#}
ON tb1.`a_msisdn`=tb2.a_msisdn
#WHERE tb1.a_msisdn=420000128321 #it is for testing
ORDER BY tb1.a_msisdn ,tb1.incoming_timestamp 
#]
) AS t1
,(SELECT @groupNumber := 0, @prev_MSISDN := NULL) var_init_subquerysa ORDER BY MSISDN,times 

) AS t2

GROUP BY gn,MSISDN

I have also included a script to create demo table and fill in some test data.

Test data preparation script: 

CREATE TABLE IF NOT EXISTS `cc_info` (
  `a_msisdn` bigint unsigned NOT NULL,
  `Incoming_timestamp` timestamp NOT NULL
);

INSERT INTO `cc_info` (`a_msisdn`, `Incoming_timestamp`) VALUES
  ('420000128321', '2018-06-26 06:26:02'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128321', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 06:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44'),
  ('420000128322', '2018-06-26 07:04:44');
Related Question