Grouping MySQL query by SUM (threshold)
I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached.
The database looks like:
date - number
2018-12-01 - 500
2018-12-02 - 400
2018-12-03 - 300
2018-12-04 - 500
2018-12-05 - 400
etc.
Expected output:
Milestone 500 = 2018-12-01
Milestone 1000 = 2018-12-03
Milestone 2000 = 2018-12-05
etc.
Edit: The milestones should show what the first date was that a certain milestone was surpassed. So 2018-12-03 is the first date that 1000 was surpassed.
The 'milestones' won't be the same distance from each other in every occasion. So ideally I'm looking for a query that could combine SUM with IF and GROUP possibly?
I have been Googling for hours, but at a loss how I can do this, other than looping through all the results separately and counting the total values + if statements, which is resource consuming. If at all possible, I'd like to keep it within the query.
Thank you for your time!
mysql mysqli
asked Dec 30 '18 at 15:04
SaschaSascha
9318
add a comment |
I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached.
The database looks like:
date - number
2018-12-01 - 500
2018-12-02 - 400
2018-12-03 - 300
2018-12-04 - 500
2018-12-05 - 400
etc.
Expected output:
Milestone 500 = 2018-12-01
Milestone 1000 = 2018-12-03
Milestone 2000 = 2018-12-05
etc.
Edit: The milestones should show what the first date was that a certain milestone was surpassed. So 2018-12-03 is the first date that 1000 was surpassed.
The 'milestones' won't be the same distance from each other in every occasion. So ideally I'm looking for a query that could combine SUM with IF and GROUP possibly?
I have been Googling for hours, but at a loss how I can do this, other than looping through all the results separately and counting the total values + if statements, which is resource consuming. If at all possible, I'd like to keep it within the query.
Thank you for your time!
mysql mysqli
asked Dec 30 '18 at 15:04
SaschaSascha
9318
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09
add a comment |
I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached.
The database looks like:
date - number
2018-12-01 - 500
2018-12-02 - 400
2018-12-03 - 300
2018-12-04 - 500
2018-12-05 - 400
etc.
Expected output:
Milestone 500 = 2018-12-01
Milestone 1000 = 2018-12-03
Milestone 2000 = 2018-12-05
etc.
Edit: The milestones should show what the first date was that a certain milestone was surpassed. So 2018-12-03 is the first date that 1000 was surpassed.
The 'milestones' won't be the same distance from each other in every occasion. So ideally I'm looking for a query that could combine SUM with IF and GROUP possibly?
I have been Googling for hours, but at a loss how I can do this, other than looping through all the results separately and counting the total values + if statements, which is resource consuming. If at all possible, I'd like to keep it within the query.
Thank you for your time!
mysql mysqli
asked Dec 30 '18 at 15:04
SaschaSascha
9318
I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached.
The database looks like:
date - number
2018-12-01 - 500
2018-12-02 - 400
2018-12-03 - 300
2018-12-04 - 500
2018-12-05 - 400
etc.
Expected output:
Milestone 500 = 2018-12-01
Milestone 1000 = 2018-12-03
Milestone 2000 = 2018-12-05
etc.
Edit: The milestones should show what the first date was that a certain milestone was surpassed. So 2018-12-03 is the first date that 1000 was surpassed.
The 'milestones' won't be the same distance from each other in every occasion. So ideally I'm looking for a query that could combine SUM with IF and GROUP possibly?
I have been Googling for hours, but at a loss how I can do this, other than looping through all the results separately and counting the total values + if statements, which is resource consuming. If at all possible, I'd like to keep it within the query.
Thank you for your time!
mysql mysqli
mysql mysqli
asked Dec 30 '18 at 15:04
SaschaSascha
9318
asked Dec 30 '18 at 15:04
SaschaSascha
9318
edited Dec 30 '18 at 15:11
Sascha
asked Dec 30 '18 at 15:04
SaschaSascha
9318
asked Dec 30 '18 at 15:04
SaschaSascha
9318
asked Dec 30 '18 at 15:04
SaschaSascha
9318
9318
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09
add a comment |
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09
add a comment |
1 Answer
1
active
oldest
votes
E.g.:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);
INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);
SELECT MIN(CASE WHEN total >= 500 THEN date END) milestone_500
, MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000
, MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000
FROM
(
SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
) a;
+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01 | 2018-12-03 | 2018-12-05 |
+---------------+----------------+----------------+
edited Dec 30 '18 at 16:19
sticky bit
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
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%2f53978706%2fgrouping-mysql-query-by-sum-threshold%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
E.g.:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);
INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);
SELECT MIN(CASE WHEN total >= 500 THEN date END) milestone_500
, MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000
, MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000
FROM
(
SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
) a;
+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01 | 2018-12-03 | 2018-12-05 |
+---------------+----------------+----------------+
edited Dec 30 '18 at 16:19
sticky bit
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
add a comment |
E.g.:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);
INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);
SELECT MIN(CASE WHEN total >= 500 THEN date END) milestone_500
, MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000
, MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000
FROM
(
SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
) a;
+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01 | 2018-12-03 | 2018-12-05 |
+---------------+----------------+----------------+
edited Dec 30 '18 at 16:19
sticky bit
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
add a comment |
E.g.:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);
INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);
SELECT MIN(CASE WHEN total >= 500 THEN date END) milestone_500
, MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000
, MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000
FROM
(
SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
) a;
+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01 | 2018-12-03 | 2018-12-05 |
+---------------+----------------+----------------+
edited Dec 30 '18 at 16:19
sticky bit
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
E.g.:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);
INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);
SELECT MIN(CASE WHEN total >= 500 THEN date END) milestone_500
, MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000
, MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000
FROM
(
SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
) a;
+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01 | 2018-12-03 | 2018-12-05 |
+---------------+----------------+----------------+
edited Dec 30 '18 at 16:19
sticky bit
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
edited Dec 30 '18 at 16:19
sticky bit
15k101632
edited Dec 30 '18 at 16:19
sticky bit
15k101632
edited Dec 30 '18 at 16:19
sticky bit
15k101632
15k101632
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
answered Dec 30 '18 at 15:19
StrawberryStrawberry
25.9k83149
25.9k83149
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%2f53978706%2fgrouping-mysql-query-by-sum-threshold%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
Your example is incomplete. I cannot reproduce your results. Milestone 1000 is 2018-12-03? Why?
– DevilaN
Dec 30 '18 at 15:08
Sorry, about that, happy to clarify: the numbers add up. So 2018-12-03 would be the first date where the summed numbers surpass 1000.
– Sascha
Dec 30 '18 at 15:09