MySQL. Multiple information into one row

Question

I have a question. For everyone to understand this and an eventual answer I'll just show you. I have one table that's looking like this:

model   | price

model1  |  10
model2  |  15
model3  |  20
model4  |  25
model5  |  30

And I want my new table to look like this:

                models                  | total
model1, model2, model3, model4, model5  |  100

How can I do that? I heard there is a group_concat() function but I also heard that is not indicated to do that. Can someone explain?

In what way is your desired resultset be more useful to your application than the underlying data? — eggyal, May 20 '14 at 20:25
@MKhalidJunaid I didn't see that when I googled it. Thanks for notifying me. — MrSilent, May 20 '14 at 20:28
@MrSilent if you are going to use group_concat then also read it [*limitations*](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) and how to overcome — M Khalid Junaid, May 20 '14 at 20:42

score 3 · Accepted Answer · answered May 20 '14 at 20:27

3

Yes, you can use GROUP_CONCAT():

SELECT GROUP_CONCAT(model) models, SUM(price) total FROM my_table

See it on sqlfiddle.

But the resulting column is a delimited string, which is obviously less structured (and therefore less useful in software) than an ungrouped recordset.

answered May 20 '14 at 20:27

eggyal

122,705
18
212
237

1

OP has shown only sample dataset which is not too big but you should add the brief regarding group_concat limitation if you are suggesting it in your answer – M Khalid Junaid May 20 '14 at 20:41

MySQL. Multiple information into one row

1 Answers1