Sum of sales by customer from two monthly sales tables - efficient query
0
looking for most efficient query to sum the cost by customer for both months - there are two tables, one for Jan and one for Feb.
Would I do an inner join or a proc sql? Looking for help. Thanks!
Added an image above because columns didn't stay in order when i typed below.
desired result:
cust 1 total from both tables
cust 2 total from both tables
cust 3 total from both tables
mo cust $ item
jan cust1 10 shoe
jan cust1 20 coat
jan cust1 30 misc
jan cust2 11 shoe
jan cust2 22 top
jan cust3 30 jean
mo cust $ item
feb cust1 15 misc
feb cust1 20 misc
feb cust1 25 jean
feb cust2 35 jean
feb cust3 12 top
feb cust3 30 top
sql-server edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
add a comment |
0
looking for most efficient query to sum the cost by customer for both months - there are two tables, one for Jan and one for Feb.
Would I do an inner join or a proc sql? Looking for help. Thanks!
Added an image above because columns didn't stay in order when i typed below.
desired result:
cust 1 total from both tables
cust 2 total from both tables
cust 3 total from both tables
mo cust $ item
jan cust1 10 shoe
jan cust1 20 coat
jan cust1 30 misc
jan cust2 11 shoe
jan cust2 22 top
jan cust3 30 jean
mo cust $ item
feb cust1 15 misc
feb cust1 20 misc
feb cust1 25 jean
feb cust2 35 jean
feb cust3 12 top
feb cust3 30 top
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Seriously you should normalize your tables. Consider combinetableandtable2or more as one single table. After that you can have a "efficient" query
– Squirrel
Nov 17 '18 at 5:10
add a comment |
0
0
0
looking for most efficient query to sum the cost by customer for both months - there are two tables, one for Jan and one for Feb.
Would I do an inner join or a proc sql? Looking for help. Thanks!
Added an image above because columns didn't stay in order when i typed below.
desired result:
cust 1 total from both tables
cust 2 total from both tables
cust 3 total from both tables
mo cust $ item
jan cust1 10 shoe
jan cust1 20 coat
jan cust1 30 misc
jan cust2 11 shoe
jan cust2 22 top
jan cust3 30 jean
mo cust $ item
feb cust1 15 misc
feb cust1 20 misc
feb cust1 25 jean
feb cust2 35 jean
feb cust3 12 top
feb cust3 30 top
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
looking for most efficient query to sum the cost by customer for both months - there are two tables, one for Jan and one for Feb.
Would I do an inner join or a proc sql? Looking for help. Thanks!
Added an image above because columns didn't stay in order when i typed below.
desired result:
cust 1 total from both tables
cust 2 total from both tables
cust 3 total from both tables
mo cust $ item
jan cust1 10 shoe
jan cust1 20 coat
jan cust1 30 misc
jan cust2 11 shoe
jan cust2 22 top
jan cust3 30 jean
mo cust $ item
feb cust1 15 misc
feb cust1 20 misc
feb cust1 25 jean
feb cust2 35 jean
feb cust3 12 top
feb cust3 30 top
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
edited Nov 17 '18 at 5:07
Squirrel
11.8k22127
11.8k22127
asked Nov 17 '18 at 4:44
newbienewbie
175
asked Nov 17 '18 at 4:44
newbienewbie
175
asked Nov 17 '18 at 4:44
newbienewbie
175
175
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Seriously you should normalize your tables. Consider combinetableandtable2or more as one single table. After that you can have a "efficient" query
– Squirrel
Nov 17 '18 at 5:10
add a comment |
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Seriously you should normalize your tables. Consider combinetableandtable2or more as one single table. After that you can have a "efficient" query
– Squirrel
Nov 17 '18 at 5:10
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Seriously you should normalize your tables. Consider combine
table and table2 or more as one single table. After that you can have a "efficient" query– Squirrel
Nov 17 '18 at 5:10
Seriously you should normalize your tables. Consider combine
table and table2 or more as one single table. After that you can have a "efficient" query– Squirrel
Nov 17 '18 at 5:10
add a comment |
1 Answer
1
active
oldest
votes
0
Can only be as efficient as your table deign. I assume "$" isn't the actual column name:
select
cust, sum(dollars) as dollars
from (
select cust, dollars from table1
UNION ALL
select cust, dollars from table2
) d
group by
cust
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
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%2f53348296%2fsum-of-sales-by-customer-from-two-monthly-sales-tables-efficient-query%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
Can only be as efficient as your table deign. I assume "$" isn't the actual column name:
select
cust, sum(dollars) as dollars
from (
select cust, dollars from table1
UNION ALL
select cust, dollars from table2
) d
group by
cust
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
add a comment |
0
Can only be as efficient as your table deign. I assume "$" isn't the actual column name:
select
cust, sum(dollars) as dollars
from (
select cust, dollars from table1
UNION ALL
select cust, dollars from table2
) d
group by
cust
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
add a comment |
0
0
0
Can only be as efficient as your table deign. I assume "$" isn't the actual column name:
select
cust, sum(dollars) as dollars
from (
select cust, dollars from table1
UNION ALL
select cust, dollars from table2
) d
group by
cust
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
Can only be as efficient as your table deign. I assume "$" isn't the actual column name:
select
cust, sum(dollars) as dollars
from (
select cust, dollars from table1
UNION ALL
select cust, dollars from table2
) d
group by
cust
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
answered Nov 17 '18 at 7:06
Used_By_AlreadyUsed_By_Already
23k21938
23k21938
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
add a comment |
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
yes and thank you, sorry about the data, was just trying to think through the process
– newbie
Nov 17 '18 at 19:54
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%2f53348296%2fsum-of-sales-by-customer-from-two-monthly-sales-tables-efficient-query%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
Thanks for your question, it would be good thing to have the create table and insert scripts for your question and along with the expected output.
– George Joseph
Nov 17 '18 at 4:47
Seriously you should normalize your tables. Consider combine
tableandtable2or more as one single table. After that you can have a "efficient" query– Squirrel
Nov 17 '18 at 5:10