How can I simplify MySQL join rows as columns?
Currently, I have a table of class president results. I am trying to join rows as columns. Essentially, the result should provide an overview of the gender of the class president over different years.
Table, named results:
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| 1 | 2016 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 2 | 2016 | Green | Susan | G | 52 | Sandy | G | 48 |
| 3 | 2016 | Purple | Carly | G | 51 | Jax | B | 49 |
| 4 | 2018 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 5 | 2018 | Green | Ben | B | 52 | Sandy | G | 48 |
| 6 | 2018 | Purple | Amanda | G | 52 | James | B | 48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
Intended result:
+--------+------+------+
| group | 2016 | 2018 |
+--------+------+------+
| yellow | B | G |
| green | G | G |
| purple | G | B |
+--------+------+------+
Working MySQL query, modified from MySQL Join Multiple Rows as Columns:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;
My current MySQL query is too complicated and occasionally triggers a MySQL timeout. So, how can I simplify this MySQL query?
UPDATE: The year column should be dynamic and the query should work even if I add more results in future years.
mysql join
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
|
show 7 more comments
Currently, I have a table of class president results. I am trying to join rows as columns. Essentially, the result should provide an overview of the gender of the class president over different years.
Table, named results:
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| 1 | 2016 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 2 | 2016 | Green | Susan | G | 52 | Sandy | G | 48 |
| 3 | 2016 | Purple | Carly | G | 51 | Jax | B | 49 |
| 4 | 2018 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 5 | 2018 | Green | Ben | B | 52 | Sandy | G | 48 |
| 6 | 2018 | Purple | Amanda | G | 52 | James | B | 48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
Intended result:
+--------+------+------+
| group | 2016 | 2018 |
+--------+------+------+
| yellow | B | G |
| green | G | G |
| purple | G | B |
+--------+------+------+
Working MySQL query, modified from MySQL Join Multiple Rows as Columns:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;
My current MySQL query is too complicated and occasionally triggers a MySQL timeout. So, how can I simplify this MySQL query?
UPDATE: The year column should be dynamic and the query should work even if I add more results in future years.
mysql join
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
1
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Bewaregroup_concat_max_len.
– spencer7593
Nov 13 '18 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of... FROM results, I'd do... FROM ( SELECT year FROM results GROUP BY year ) vrt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column ofyearshould help with theGROUP BYperformance.
– spencer7593
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17
|
show 7 more comments
Currently, I have a table of class president results. I am trying to join rows as columns. Essentially, the result should provide an overview of the gender of the class president over different years.
Table, named results:
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| 1 | 2016 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 2 | 2016 | Green | Susan | G | 52 | Sandy | G | 48 |
| 3 | 2016 | Purple | Carly | G | 51 | Jax | B | 49 |
| 4 | 2018 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 5 | 2018 | Green | Ben | B | 52 | Sandy | G | 48 |
| 6 | 2018 | Purple | Amanda | G | 52 | James | B | 48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
Intended result:
+--------+------+------+
| group | 2016 | 2018 |
+--------+------+------+
| yellow | B | G |
| green | G | G |
| purple | G | B |
+--------+------+------+
Working MySQL query, modified from MySQL Join Multiple Rows as Columns:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;
My current MySQL query is too complicated and occasionally triggers a MySQL timeout. So, how can I simplify this MySQL query?
UPDATE: The year column should be dynamic and the query should work even if I add more results in future years.
mysql join
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
Currently, I have a table of class president results. I am trying to join rows as columns. Essentially, the result should provide an overview of the gender of the class president over different years.
Table, named results:
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| 1 | 2016 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 2 | 2016 | Green | Susan | G | 52 | Sandy | G | 48 |
| 3 | 2016 | Purple | Carly | G | 51 | Jax | B | 49 |
| 4 | 2018 | Yellow | Tom | B | 56 | Jill | G | 46 |
| 5 | 2018 | Green | Ben | B | 52 | Sandy | G | 48 |
| 6 | 2018 | Purple | Amanda | G | 52 | James | B | 48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
Intended result:
+--------+------+------+
| group | 2016 | 2018 |
+--------+------+------+
| yellow | B | G |
| green | G | G |
| purple | G | B |
+--------+------+------+
Working MySQL query, modified from MySQL Join Multiple Rows as Columns:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;
My current MySQL query is too complicated and occasionally triggers a MySQL timeout. So, how can I simplify this MySQL query?
UPDATE: The year column should be dynamic and the query should work even if I add more results in future years.
mysql join
mysql join
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
edited Nov 13 '18 at 5:07
Panda
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
asked Nov 13 '18 at 5:05
PandaPanda
6,30462742
6,30462742
1
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Bewaregroup_concat_max_len.
– spencer7593
Nov 13 '18 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of... FROM results, I'd do... FROM ( SELECT year FROM results GROUP BY year ) vrt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column ofyearshould help with theGROUP BYperformance.
– spencer7593
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17
|
show 7 more comments
1
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Bewaregroup_concat_max_len.
– spencer7593
Nov 13 '18 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of... FROM results, I'd do... FROM ( SELECT year FROM results GROUP BY year ) vrt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column ofyearshould help with theGROUP BYperformance.
– spencer7593
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17
1
1
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Beware
group_concat_max_len.– spencer7593
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Beware
group_concat_max_len.– spencer7593
Nov 13 '18 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of
... FROM results, I'd do ... FROM ( SELECT year FROM results GROUP BY year ) v rt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column of year should help with the GROUP BY performance.– spencer7593
Nov 13 '18 at 5:16
For performance improvement, I'd make the first query a little more complicated... instead of
... FROM results, I'd do ... FROM ( SELECT year FROM results GROUP BY year ) v rt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column of year should help with the GROUP BY performance.– spencer7593
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17
|
show 7 more comments
1 Answer
1
active
oldest
votes
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53274140%2fhow-can-i-simplify-mysql-join-rows-as-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
add a comment |
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
add a comment |
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
edited Nov 14 '18 at 0:33
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
answered Nov 14 '18 at 0:27
fubarfubar
9,84121531
9,84121531
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53274140%2fhow-can-i-simplify-mysql-join-rows-as-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of MySQL pivot table
– Madhur Bhaiya
Nov 13 '18 at 5:10
There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Beware
group_concat_max_len.– spencer7593
Nov 13 '18 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of
... FROM results, I'd do... FROM ( SELECT year FROM results GROUP BY year ) vrt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column ofyearshould help with theGROUP BYperformance.– spencer7593
Nov 13 '18 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows.
– fubar
Nov 13 '18 at 5:16
@spencer7593 Thanks for the suggestions, I'll try that out.
– Panda
Nov 13 '18 at 5:17