Mixing
Is there any function available in MySQL which can group the result of Dates into days wise split up
0
SQL Table Schema:
Id CaseID CounterID ActionID CurrentStart CreatedDate
973 11 13 16 2017-12-11 09:28:11 2017-12-11 09:28:11
I need a result like below
CounterID 0-30(Range 1) 31-60(Range 2) 61-90
16 22(Count) 20(Count) 18(Count)
and so on
mysql grouping
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
add a comment |
0
SQL Table Schema:
Id CaseID CounterID ActionID CurrentStart CreatedDate
973 11 13 16 2017-12-11 09:28:11 2017-12-11 09:28:11
I need a result like below
CounterID 0-30(Range 1) 31-60(Range 2) 61-90
16 22(Count) 20(Count) 18(Count)
and so on
mysql grouping
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
add a comment |
0
0
0
SQL Table Schema:
Id CaseID CounterID ActionID CurrentStart CreatedDate
973 11 13 16 2017-12-11 09:28:11 2017-12-11 09:28:11
I need a result like below
CounterID 0-30(Range 1) 31-60(Range 2) 61-90
16 22(Count) 20(Count) 18(Count)
and so on
mysql grouping
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
SQL Table Schema:
Id CaseID CounterID ActionID CurrentStart CreatedDate
973 11 13 16 2017-12-11 09:28:11 2017-12-11 09:28:11
I need a result like below
CounterID 0-30(Range 1) 31-60(Range 2) 61-90
16 22(Count) 20(Count) 18(Count)
and so on
mysql grouping
mysql grouping
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
edited Jan 2 at 14:35
Alon Eitan
11.3k63953
11.3k63953
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
asked Jan 2 at 14:32
Prasad OturkarPrasad Oturkar
31
31
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
If you want a solution with a group by here is the simplest one :
<!-- language: sql -->
select
Id,
sum(case when CurrentStart - CreatedDate <= 30 THEN 1 ELSE 0 END) INF30,
sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
sum(case when CurrentStart - CreatedDate > 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;
Here is another form I suggest to help you build such queries :
- In a sub-request, you get the Id, and three columns to know if the date is in range with this CASE : 1 if date is in range, 0 otherwise (the sub-request is named INNER_TABLE here)
- You group by Id the results of this sub-request and take the sum of the columns for the ranges.
select
INNER_TABLE.Id,
sum(INNER_TABLE.INF30) INF30,
sum(INNER_TABLE.BET3060) BET3060,
sum(INNER_TABLE.SUP60) SUP60
from (select
Id,
case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
from tablename where Id is not null) INNER_TABLE
group by INNER_TABLE.Id;
Otherwise, another simple solution :
- You take the list of different CounterId on which you want to do the group by
(select distinct Id from tablename where id is not null) as ids
- You use a sub-request in each part of the select for your count :
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)
- Apply the same for the three selected fields
select
ids.Id,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
(select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;
Where tablename is the name of your table of course :)
answered Jan 2 at 15:00
Julien B.Julien B.
596
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');
});
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%2f54008155%2fis-there-any-function-available-in-mysql-which-can-group-the-result-of-dates-int%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
0
If you want a solution with a group by here is the simplest one :
<!-- language: sql -->
select
Id,
sum(case when CurrentStart - CreatedDate <= 30 THEN 1 ELSE 0 END) INF30,
sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
sum(case when CurrentStart - CreatedDate > 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;
Here is another form I suggest to help you build such queries :
- In a sub-request, you get the Id, and three columns to know if the date is in range with this CASE : 1 if date is in range, 0 otherwise (the sub-request is named INNER_TABLE here)
- You group by Id the results of this sub-request and take the sum of the columns for the ranges.
select
INNER_TABLE.Id,
sum(INNER_TABLE.INF30) INF30,
sum(INNER_TABLE.BET3060) BET3060,
sum(INNER_TABLE.SUP60) SUP60
from (select
Id,
case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
from tablename where Id is not null) INNER_TABLE
group by INNER_TABLE.Id;
Otherwise, another simple solution :
- You take the list of different CounterId on which you want to do the group by
(select distinct Id from tablename where id is not null) as ids
- You use a sub-request in each part of the select for your count :
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)
- Apply the same for the three selected fields
select
ids.Id,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
(select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;
Where tablename is the name of your table of course :)
answered Jan 2 at 15:00
Julien B.Julien B.
596
add a comment |
0
If you want a solution with a group by here is the simplest one :
<!-- language: sql -->
select
Id,
sum(case when CurrentStart - CreatedDate <= 30 THEN 1 ELSE 0 END) INF30,
sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
sum(case when CurrentStart - CreatedDate > 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;
Here is another form I suggest to help you build such queries :
- In a sub-request, you get the Id, and three columns to know if the date is in range with this CASE : 1 if date is in range, 0 otherwise (the sub-request is named INNER_TABLE here)
- You group by Id the results of this sub-request and take the sum of the columns for the ranges.
select
INNER_TABLE.Id,
sum(INNER_TABLE.INF30) INF30,
sum(INNER_TABLE.BET3060) BET3060,
sum(INNER_TABLE.SUP60) SUP60
from (select
Id,
case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
from tablename where Id is not null) INNER_TABLE
group by INNER_TABLE.Id;
Otherwise, another simple solution :
- You take the list of different CounterId on which you want to do the group by
(select distinct Id from tablename where id is not null) as ids
- You use a sub-request in each part of the select for your count :
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)
- Apply the same for the three selected fields
select
ids.Id,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
(select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;
Where tablename is the name of your table of course :)
answered Jan 2 at 15:00
Julien B.Julien B.
596
add a comment |
0
0
0
If you want a solution with a group by here is the simplest one :
<!-- language: sql -->
select
Id,
sum(case when CurrentStart - CreatedDate <= 30 THEN 1 ELSE 0 END) INF30,
sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
sum(case when CurrentStart - CreatedDate > 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;
Here is another form I suggest to help you build such queries :
- In a sub-request, you get the Id, and three columns to know if the date is in range with this CASE : 1 if date is in range, 0 otherwise (the sub-request is named INNER_TABLE here)
- You group by Id the results of this sub-request and take the sum of the columns for the ranges.
select
INNER_TABLE.Id,
sum(INNER_TABLE.INF30) INF30,
sum(INNER_TABLE.BET3060) BET3060,
sum(INNER_TABLE.SUP60) SUP60
from (select
Id,
case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
from tablename where Id is not null) INNER_TABLE
group by INNER_TABLE.Id;
Otherwise, another simple solution :
- You take the list of different CounterId on which you want to do the group by
(select distinct Id from tablename where id is not null) as ids
- You use a sub-request in each part of the select for your count :
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)
- Apply the same for the three selected fields
select
ids.Id,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
(select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;
Where tablename is the name of your table of course :)
answered Jan 2 at 15:00
Julien B.Julien B.
596
If you want a solution with a group by here is the simplest one :
<!-- language: sql -->
select
Id,
sum(case when CurrentStart - CreatedDate <= 30 THEN 1 ELSE 0 END) INF30,
sum(case when CurrentStart - CreatedDate BETWEEN 31 AND 60 THEN 1 ELSE 0 END) BET3060,
sum(case when CurrentStart - CreatedDate > 60 THEN 1 ELSE 0 END) SUP60
from tablename where Id is not null group by Id;
Here is another form I suggest to help you build such queries :
- In a sub-request, you get the Id, and three columns to know if the date is in range with this CASE : 1 if date is in range, 0 otherwise (the sub-request is named INNER_TABLE here)
- You group by Id the results of this sub-request and take the sum of the columns for the ranges.
select
INNER_TABLE.Id,
sum(INNER_TABLE.INF30) INF30,
sum(INNER_TABLE.BET3060) BET3060,
sum(INNER_TABLE.SUP60) SUP60
from (select
Id,
case when CreatedDate - CurrentStart <= 30 THEN 1 ELSE 0 END INF30,
case when CreatedDate - CurrentStart BETWEEN 31 AND 60 THEN 1 ELSE 0 END BET3060,
case when CreatedDate - CurrentStart > 60 THEN 1 ELSE 0 END SUP60
from tablename where Id is not null) INNER_TABLE
group by INNER_TABLE.Id;
Otherwise, another simple solution :
- You take the list of different CounterId on which you want to do the group by
(select distinct Id from tablename where id is not null) as ids
- You use a sub-request in each part of the select for your count :
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate < 30)
- Apply the same for the three selected fields
select
ids.Id,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate <= 30) INF_30,
(select count(*) from tablename where Id = ids.Id and CurrentStart - CreatedDate > 30 and CurrentStart - CreatedDate <= 60) BET_30_60,
(select count(*) from tablename where Id= ids.Id and CurrentStart - CreatedDate > 60) SUP_60
from (select distinct Id from tablename where Id is not null) ids;
Where tablename is the name of your table of course :)
answered Jan 2 at 15:00
Julien B.Julien B.
596
edited Jan 3 at 8:51
answered Jan 2 at 15:00
Julien B.Julien B.
596
answered Jan 2 at 15:00
Julien B.Julien B.
596
answered Jan 2 at 15:00
Julien B.Julien B.
596
596
add a comment |
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');
});
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%2f54008155%2fis-there-any-function-available-in-mysql-which-can-group-the-result-of-dates-int%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
