0

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

  • 1
    1064 is a parse error so you should have the proper line number and "occurs near xxx" that will lead you to the source error. It yields the issue near var_init_subquerysa which indeed makes no sense – Xenos Jul 3 '18 at 12:24
  • @xenos Error Code: 1064 Erreur de syntaxe près de ') as t2 ) – zahid Jul 3 '18 at 12:26
  • @xenos that's why I am confused – zahid Jul 3 '18 at 12:28
  • Edit your title to summarize the specific issue of your Question. – Basil Bourque Jul 5 '18 at 6:39
0

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');
  • there is no problem of brackets – zahid Jul 5 '18 at 4:52
  • @zahid - I have corrected my answer, and have provided scripts and a link to a working SQL Fiddle script – Milan Jul 5 '18 at 7:17
0

Right before

) AS t2

is

 ORDER BY  MSISDN,times

which does not make sense. If times is to be a table, then it should not be JOINed (with a comma) to another table with an ORDER BY clause. (Unless you put parens around the subquery.)

Or was that supposed to be a period, not a comma?: MSISDN.times

When Error 1064 says "near", it means it.

  • times is not a table. it is a column – zahid Jul 4 '18 at 6:28
  • Then the problem is a simple typo. – Rick James Jul 4 '18 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.