Split one row into multiple rows according to `sum` column

schema

In table track_play_counts there is a column named play_count which is a sum of individual track_play_count. How to split the play_count column into different rows which will have play_count value as 1 only? The table contains thousands of data, So I can not do it manually.

For example, if play_count is 10 then there should be 10 rows with 1 play_count. How can I achieve it using mysql or laravel?

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

Why do you want to do this?

– Nick
Jan 1 at 4:56

because of changing requirements of the field.

– unknown
Jan 1 at 5:13

add a comment |

schema

For example, if play_count is 10 then there should be 10 rows with 1 play_count. How can I achieve it using mysql or laravel?

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

Why do you want to do this?

– Nick
Jan 1 at 4:56

because of changing requirements of the field.

– unknown
Jan 1 at 5:13

add a comment |

schema

For example, if play_count is 10 then there should be 10 rows with 1 play_count. How can I achieve it using mysql or laravel?

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

schema

For example, if play_count is 10 then there should be 10 rows with 1 play_count. How can I achieve it using mysql or laravel?

mysql laravel-5.6

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

edited Jan 2 at 11:47

Nick

31.2k121942

edited Jan 2 at 11:47

Nick

31.2k121942

edited Jan 2 at 11:47

Nick

31.2k121942

asked Jan 1 at 4:32

unknown

207

asked Jan 1 at 4:32

unknown

207

asked Jan 1 at 4:32

unknown

207

Why do you want to do this?

– Nick
Jan 1 at 4:56

because of changing requirements of the field.

– unknown
Jan 1 at 5:13

add a comment |

Why do you want to do this?

– Nick
Jan 1 at 4:56

because of changing requirements of the field.

– unknown
Jan 1 at 5:13

Why do you want to do this?

– Nick
Jan 1 at 4:56

because of changing requirements of the field.

– unknown
Jan 1 at 5:13

add a comment |

1 Answer
1

active

oldest

votes

You can use this query to create a new table which has one row for each individual track_play_count.

CREATE TABLE new_play_counts (id INT AUTO_INCREMENT PRIMARY KEY) AS

SELECT track_id, 1 AS play_count

FROM (SELECT n10.n*10 + n.n AS num

      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10

      JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n

      ) numbers

JOIN track_play_counts p ON p.play_count > numbers.num

ORDER BY p.Id, numbers.num

Demo on dbfiddle

Notes

I've assumed that the maximum value of play_count is 99, if it is higher than that you would need to increase the size of the numbers table by JOINing another digit to it.

I've also assumed that you would want to give each row in the new table a distinct id value, if not, you would remove (id INT AUTO_INCREMENT PRIMARY KEY) from the first line of the query and change the SELECT to SELECT id, track_id, 1 AS play_count

answered Jan 1 at 6:02

Nick

31.2k121942

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993024%2fsplit-one-row-into-multiple-rows-according-to-sum-column%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You can use this query to create a new table which has one row for each individual track_play_count.

CREATE TABLE new_play_counts (id INT AUTO_INCREMENT PRIMARY KEY) AS

SELECT track_id, 1 AS play_count

FROM (SELECT n10.n*10 + n.n AS num

      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10

      JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n

      ) numbers

JOIN track_play_counts p ON p.play_count > numbers.num

ORDER BY p.Id, numbers.num

Demo on dbfiddle

Notes

I've assumed that the maximum value of play_count is 99, if it is higher than that you would need to increase the size of the numbers table by JOINing another digit to it.

I've also assumed that you would want to give each row in the new table a distinct id value, if not, you would remove (id INT AUTO_INCREMENT PRIMARY KEY) from the first line of the query and change the SELECT to SELECT id, track_id, 1 AS play_count

answered Jan 1 at 6:02

Nick

31.2k121942

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

add a comment |

You can use this query to create a new table which has one row for each individual track_play_count.

CREATE TABLE new_play_counts (id INT AUTO_INCREMENT PRIMARY KEY) AS

SELECT track_id, 1 AS play_count

FROM (SELECT n10.n*10 + n.n AS num

      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10

      JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n

      ) numbers

JOIN track_play_counts p ON p.play_count > numbers.num

ORDER BY p.Id, numbers.num

Demo on dbfiddle

Notes

I've assumed that the maximum value of play_count is 99, if it is higher than that you would need to increase the size of the numbers table by JOINing another digit to it.

I've also assumed that you would want to give each row in the new table a distinct id value, if not, you would remove (id INT AUTO_INCREMENT PRIMARY KEY) from the first line of the query and change the SELECT to SELECT id, track_id, 1 AS play_count

answered Jan 1 at 6:02

Nick

31.2k121942

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

add a comment |

You can use this query to create a new table which has one row for each individual track_play_count.

CREATE TABLE new_play_counts (id INT AUTO_INCREMENT PRIMARY KEY) AS

SELECT track_id, 1 AS play_count

FROM (SELECT n10.n*10 + n.n AS num

      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10

      JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n

      ) numbers

JOIN track_play_counts p ON p.play_count > numbers.num

ORDER BY p.Id, numbers.num

Demo on dbfiddle

Notes

I've assumed that the maximum value of play_count is 99, if it is higher than that you would need to increase the size of the numbers table by JOINing another digit to it.

I've also assumed that you would want to give each row in the new table a distinct id value, if not, you would remove (id INT AUTO_INCREMENT PRIMARY KEY) from the first line of the query and change the SELECT to SELECT id, track_id, 1 AS play_count

answered Jan 1 at 6:02

Nick

31.2k121942

You can use this query to create a new table which has one row for each individual track_play_count.

CREATE TABLE new_play_counts (id INT AUTO_INCREMENT PRIMARY KEY) AS

SELECT track_id, 1 AS play_count

FROM (SELECT n10.n*10 + n.n AS num

      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10

      JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3

                          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6

                          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n

      ) numbers

JOIN track_play_counts p ON p.play_count > numbers.num

ORDER BY p.Id, numbers.num

Demo on dbfiddle

Notes

I've assumed that the maximum value of play_count is 99, if it is higher than that you would need to increase the size of the numbers table by JOINing another digit to it.

I've also assumed that you would want to give each row in the new table a distinct id value, if not, you would remove (id INT AUTO_INCREMENT PRIMARY KEY) from the first line of the query and change the SELECT to SELECT id, track_id, 1 AS play_count

answered Jan 1 at 6:02

Nick

31.2k121942

answered Jan 1 at 6:02

Nick

31.2k121942

answered Jan 1 at 6:02

Nick

31.2k121942

answered Jan 1 at 6:02

Nick

31.2k121942

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

add a comment |

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

Thanks for reply, solved problem using laravel eloquent query.

– unknown
Jan 1 at 8:59

You should post that as an answer and accept it to help other people who might have the same problem.

– Nick
Jan 1 at 10:42

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk