How to fill GROUP_CONCAT() with 0's when JOIN data missing
I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.
Here's the current output:
[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21
My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:
[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0
WITH RECURSIVE dates (date) AS
(
SELECT :startingDate
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
)
SELECT
COALESCE(daily_stats.date, dates.date) AS label,
GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
FROM dates
LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
GROUP BY label;
I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?
My table:
CREATE TABLE `daily_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`stat` int(11) NOT NULL,
`value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql-8.0
asked 2 mins ago
WebnetWebnet
71119
add a comment |
I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.
Here's the current output:
[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21
My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:
[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0
WITH RECURSIVE dates (date) AS
(
SELECT :startingDate
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
)
SELECT
COALESCE(daily_stats.date, dates.date) AS label,
GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
FROM dates
LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
GROUP BY label;
I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?
My table:
CREATE TABLE `daily_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`stat` int(11) NOT NULL,
`value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql-8.0
asked 2 mins ago
WebnetWebnet
71119
add a comment |
I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.
Here's the current output:
[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21
My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:
[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0
WITH RECURSIVE dates (date) AS
(
SELECT :startingDate
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
)
SELECT
COALESCE(daily_stats.date, dates.date) AS label,
GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
FROM dates
LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
GROUP BY label;
I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?
My table:
CREATE TABLE `daily_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`stat` int(11) NOT NULL,
`value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql-8.0
asked 2 mins ago
WebnetWebnet
71119
I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.
Here's the current output:
[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21
My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:
[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0
WITH RECURSIVE dates (date) AS
(
SELECT :startingDate
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
)
SELECT
COALESCE(daily_stats.date, dates.date) AS label,
GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
FROM dates
LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
GROUP BY label;
I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?
My table:
CREATE TABLE `daily_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`stat` int(11) NOT NULL,
`value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql-8.0
mysql-8.0
asked 2 mins ago
WebnetWebnet
71119
asked 2 mins ago
WebnetWebnet
71119
asked 2 mins ago
WebnetWebnet
71119
asked 2 mins ago
WebnetWebnet
71119
asked 2 mins ago
WebnetWebnet
71119
71119
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f228802%2fhow-to-fill-group-concat-with-0s-when-join-data-missing%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f228802%2fhow-to-fill-group-concat-with-0s-when-join-data-missing%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
