Query Using Group By for multiple columns with multiple conditions
1
I have the table
account_no | name | date | amount
1101 | A | 2018-11-20 | 50
1101 | A | 2018-11-20 | 20
1102 | B | 2018-11-20 | 30
1101 | A | 2018-11-19 | 100
1101 | A | 2018-11-18 | 80
1102 | B | 2018-11-19 | 70
And the I want the following output
account_no | name | group(today) | group(month)
1101 | A | 70 | 250
1102 | B | 30 | 100
either I am getting today's sum or month's sum not both in a single row.
What I have tried so far
select account_no, sum(today) from account group by account_no having date = '<today>'
select account_no, sum(today) from account group by account_no having date between '<firstDay>' and '<today>'
I can think of using UNION ALL but giving duplicate rows.
mysql
edited Nov 21 '18 at 17:07
James Z
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
add a comment |
1
I have the table
account_no | name | date | amount
1101 | A | 2018-11-20 | 50
1101 | A | 2018-11-20 | 20
1102 | B | 2018-11-20 | 30
1101 | A | 2018-11-19 | 100
1101 | A | 2018-11-18 | 80
1102 | B | 2018-11-19 | 70
And the I want the following output
account_no | name | group(today) | group(month)
1101 | A | 70 | 250
1102 | B | 30 | 100
either I am getting today's sum or month's sum not both in a single row.
What I have tried so far
select account_no, sum(today) from account group by account_no having date = '<today>'
select account_no, sum(today) from account group by account_no having date between '<firstDay>' and '<today>'
I can think of using UNION ALL but giving duplicate rows.
mysql
edited Nov 21 '18 at 17:07
James Z
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
what is the today mean?
– fa06
Nov 21 '18 at 6:34
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43
add a comment |
1
1
1
I have the table
account_no | name | date | amount
1101 | A | 2018-11-20 | 50
1101 | A | 2018-11-20 | 20
1102 | B | 2018-11-20 | 30
1101 | A | 2018-11-19 | 100
1101 | A | 2018-11-18 | 80
1102 | B | 2018-11-19 | 70
And the I want the following output
account_no | name | group(today) | group(month)
1101 | A | 70 | 250
1102 | B | 30 | 100
either I am getting today's sum or month's sum not both in a single row.
What I have tried so far
select account_no, sum(today) from account group by account_no having date = '<today>'
select account_no, sum(today) from account group by account_no having date between '<firstDay>' and '<today>'
I can think of using UNION ALL but giving duplicate rows.
mysql
edited Nov 21 '18 at 17:07
James Z
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
I have the table
account_no | name | date | amount
1101 | A | 2018-11-20 | 50
1101 | A | 2018-11-20 | 20
1102 | B | 2018-11-20 | 30
1101 | A | 2018-11-19 | 100
1101 | A | 2018-11-18 | 80
1102 | B | 2018-11-19 | 70
And the I want the following output
account_no | name | group(today) | group(month)
1101 | A | 70 | 250
1102 | B | 30 | 100
either I am getting today's sum or month's sum not both in a single row.
What I have tried so far
select account_no, sum(today) from account group by account_no having date = '<today>'
select account_no, sum(today) from account group by account_no having date between '<firstDay>' and '<today>'
I can think of using UNION ALL but giving duplicate rows.
mysql
mysql
edited Nov 21 '18 at 17:07
James Z
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
edited Nov 21 '18 at 17:07
James Z
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
edited Nov 21 '18 at 17:07
James Z
11.2k71935
edited Nov 21 '18 at 17:07
James Z
11.2k71935
edited Nov 21 '18 at 17:07
James Z
11.2k71935
11.2k71935
asked Nov 21 '18 at 6:21
AbdulAbdul
227
asked Nov 21 '18 at 6:21
AbdulAbdul
227
asked Nov 21 '18 at 6:21
AbdulAbdul
227
227
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
what is the today mean?
– fa06
Nov 21 '18 at 6:34
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43
add a comment |
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
what is the today mean?
– fa06
Nov 21 '18 at 6:34
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
what is the today mean?
– fa06
Nov 21 '18 at 6:34
what is the today mean?
– fa06
Nov 21 '18 at 6:34
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43
add a comment |
1 Answer
1
active
oldest
votes
0
You can try using conditional aggregation
select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
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%2f53406297%2fquery-using-group-by-for-multiple-columns-with-multiple-conditions%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
You can try using conditional aggregation
select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
add a comment |
0
You can try using conditional aggregation
select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
add a comment |
0
0
0
You can try using conditional aggregation
select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
You can try using conditional aggregation
select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
answered Nov 21 '18 at 6:45
fa06fa06
15.8k21018
15.8k21018
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
add a comment |
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
1
1
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
It worked like a Charm. Thanks a Million. I did another work around. Just have a look. But your's is Optimum.
– Abdul
Nov 21 '18 at 7:15
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
Select account_no, name, sum(today_total) as Today, sum(month_total) as Month From ( Select account_no, name, sum(amount) as today_total, null as month_total from account where date>=<today> group by account_no UNION ALL Select account_no, name, null as today_total, sum(amount) as month_total from account where date between <firstDay> and <today> group by account_no ) as t1 group by account_no
– Abdul
Nov 21 '18 at 7:16
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
@Abdul, this will work too - since you can do it in a single query, so that is the more optimum
– fa06
Nov 21 '18 at 7:17
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
Yes @fa06 you are right. your solution is more optimum. Thanks once again. You saved my day. My project demands more complex queries like this. Now I found the way. will play with.
– Abdul
Nov 21 '18 at 7:22
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%2f53406297%2fquery-using-group-by-for-multiple-columns-with-multiple-conditions%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
You could show a bit of effort and publish what you have done so far. If you don't know where to start search for mysql conditional aggregation.
– P.Salmon
Nov 21 '18 at 6:29
what is the today mean?
– fa06
Nov 21 '18 at 6:34
today means currenDate - Here I want currentDate sum and whole month till today sum
– Abdul
Nov 21 '18 at 6:42
But your expected output is not same as your sample data - for A there is only 50 for currentdate
– fa06
Nov 21 '18 at 6:43