How to concat string in mysql?

Question

WHat i want is concate the static string to dataset i want to add string as min, avg, max to the fetched data. when i m trying give some error?

my query

select m.medTitle, 
min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m on s.dmtMedId = m.medId
group by m.medTitle
union all
select m.medTitle, 
avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m on s.dmtMedId = m.medId
group by m.medTitle
union all
select m.medTitle, 
  max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.dmtMedId = m.medId
group by m.medTitle
order by medTitle,  playtime;

what i am trying.

select concat(m.medTitle,'minimum'), 
  min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
union all

select concat(m.medTitle,'average'), 
  avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
union all

select concat(m.medTitle,'maximum'),  
  max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
order by medTitle,  playtime

Both your queries doesn't compile. Either post a valid query and state what you want to change, or show us the tables structure and the requested output. — Nir Alfasi
– Nir Alfasi, Commented Sep 18, 2012 at 5:18
see my this post stackoverflow.com/questions/12465825/… and sqlfiddle.com/#!2/cf37a/9 in this example first records for casfung are there i just want to add min, avg , max to casfung result date like casfung(min) casfung(avg) casfung(max) — Manish Malviya
– Manish Malviya, Commented Sep 18, 2012 at 5:21
Error Unknown column 'medTitle' in 'order clause': select concat(m.medTitle,'minimum'), min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime from tbldoctormediatracktest s left join tblmedia m on s.MedId = m.MedId group by m.medTitle union all select concat(m.medTitle,'average'), avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime from tbldoctormediatracktest s left — Manish Malviya
– Manish Malviya, Commented Sep 18, 2012 at 5:27

Joe G Joseph · Accepted Answer · 2012-09-18 06:20:11Z

2

try this:

select playtime,type 
from(
select m.MedId, 
  min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime,
  concat(m.medTitle,' (minimum)') as type
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.MedId
union all

select m.MedId, 
  avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime,
  concat(m.medTitle,' (average)') as type
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.MedId
union all

select m.MedId,  
  max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime,
  concat(m.medTitle,' (maximum)') as type 
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.MedId)a
order by MedId,  playtime

SQL Fiddle domo 1

SQl Fiddle Demo 2

SQL Fiddle Demo 3

edited Sep 18, 2012 at 6:20

answered Sep 18, 2012 at 5:55

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manish Malviya Over a year ago

can we group by and order by m.MedId?

Joe G Joseph Over a year ago

you want 78 Casfung (maximum) in the ouptput?

Shashwat · Accepted Answer · 2012-09-18 05:34:54Z

0

select concat(m.medTitle, 'min') medTitle,
  min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle

union all

select concat(m.medTitle, 'avg') medTitle, 
  avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle

union all

select m.medTitle || 'max' medTitle, 
  max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
  on s.MedId = m.MedId
group by m.medTitle
order by medTitle, playtime

Updated your query from the link. I noticed that you need to give the alias for m.medTitle after concatenating.

Any of concat(m.medTitle, 'min') or m.medTitle || 'min' can be used for concatenation.

edited Sep 18, 2012 at 5:34

answered Sep 18, 2012 at 5:13

Shashwat

2,6888 gold badges44 silver badges58 bronze badges

4 Comments

Manish Malviya Over a year ago

this min max avg should add as suffix. suppose first record comes as 'boxer' then min should be added to boxer like boxermin. sqlfiddle.com/#!2/27a4b/8

Manish Malviya Over a year ago

some how you got my problem but not returning last query value max

Shashwat Over a year ago

You can update the concatenated string. I did 'min' for all the three queries. Anyway, updated my answer again.

Manish Malviya Over a year ago

can we group by and order by m.MedId?

Collectives™ on Stack Overflow

How to concat string in mysql?

2 Answers 2

SQL Fiddle domo 1

SQl Fiddle Demo 2

SQL Fiddle Demo 3

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related