Q:

How to transform a MSSQL CTE query to MySQL 5.7? 

I have one SQL queries
with temp as 
(
select min(ms_date) as start, max(ms_date) as [end], count(sessionid) 'visitor_count',category,convert(varchar, ms_date , 106) 'ms_date'        
from temp_tbltrack  where category =@id         
group by category,convert(varchar, ms_date , 106)
)
select category,ms_date,CASE WHEN datediff(ss,temp.start,temp.[end]) <>0 THEN datediff(ss,temp.start,temp.[end]) ELSE 45 END 'Timespan',visitor_count from temp
order by convert(datetime,ms_date)

I converted into Mysql
select temp from 
(
select min(ms_date) as start, max(ms_date) as `end`, count(sessionid) 'visitor_count',category,date_format (ms_date , 106) 'ms_date'        
from temp_tbltrack  where category =p_id         
group by category,date_format (ms_date , 106)
)
select category,ms_date,CASE WHEN timestampdiff(ss,temp.start,temp.[end]) <>0 THEN timestampdiff(ss,temp.start,temp.[end]) ELSE 45 END 'Timespan',visitor_count from temp
order by convert(ms_date, datetime);  

How I convert CTE in Mysql 5.7 version

A:

I don't think a subquery is needed:
select category,
       format(ms_date, '%d %b %Y') as ms_date,
       (case when max(ms_date) = min(ms_date)
             then 45
             else timestampdiff(second, min(ms_date), max(ms_date))
        end) as timestamp
       count(sessionid) as `visitor_count`,
from temp_tbltrack 
where category = @id         
group by category, format(ms_date, '%d %b %Y')
order by min(ms_date);

