Oracle SQL Results duplicating with join
I am trying to use the following code :
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(b.REVISED_QTY_DUE) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned'
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
GROUP BY a.part_no
However I am seeing duplicated results, I know this is to do with the join but not sure how to fix :(
Results
The results I expect to see are,
Tot_Ord = 277
(6 instances of 23, 3 instances of 46, 1 instance of 1) 10 lines in total
S_O_Tot = 46
(2 instances of 23) 2 lines in Total
Any help would be greatly appreciated,
Thanks,
Jamie
oracle
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
add a comment |
I am trying to use the following code :
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(b.REVISED_QTY_DUE) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned'
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
GROUP BY a.part_no
However I am seeing duplicated results, I know this is to do with the join but not sure how to fix :(
Results
The results I expect to see are,
Tot_Ord = 277
(6 instances of 23, 3 instances of 46, 1 instance of 1) 10 lines in total
S_O_Tot = 46
(2 instances of 23) 2 lines in Total
Any help would be greatly appreciated,
Thanks,
Jamie
oracle
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
3
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55
add a comment |
I am trying to use the following code :
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(b.REVISED_QTY_DUE) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned'
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
GROUP BY a.part_no
However I am seeing duplicated results, I know this is to do with the join but not sure how to fix :(
Results
The results I expect to see are,
Tot_Ord = 277
(6 instances of 23, 3 instances of 46, 1 instance of 1) 10 lines in total
S_O_Tot = 46
(2 instances of 23) 2 lines in Total
Any help would be greatly appreciated,
Thanks,
Jamie
oracle
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
I am trying to use the following code :
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(b.REVISED_QTY_DUE) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned'
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
GROUP BY a.part_no
However I am seeing duplicated results, I know this is to do with the join but not sure how to fix :(
Results
The results I expect to see are,
Tot_Ord = 277
(6 instances of 23, 3 instances of 46, 1 instance of 1) 10 lines in total
S_O_Tot = 46
(2 instances of 23) 2 lines in Total
Any help would be greatly appreciated,
Thanks,
Jamie
oracle
oracle
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
edited Nov 21 '18 at 12:16
Jérôme Teisseire
1,03611020
1,03611020
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
asked Nov 21 '18 at 11:45
Jamie BairdJamie Baird
1
1
3
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55
add a comment |
3
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55
3
3
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55
add a comment |
1 Answer
1
active
oldest
votes
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(nvl(b.REVISED_QTY_DUE,0)) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON (a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned')
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
I have deleted grouping because you are filtering on this filed anyway
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
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%2f53411348%2foracle-sql-results-duplicating-with-join%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
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(nvl(b.REVISED_QTY_DUE,0)) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON (a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned')
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
I have deleted grouping because you are filtering on this filed anyway
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
add a comment |
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(nvl(b.REVISED_QTY_DUE,0)) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON (a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned')
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
I have deleted grouping because you are filtering on this filed anyway
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
add a comment |
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(nvl(b.REVISED_QTY_DUE,0)) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON (a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned')
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
I have deleted grouping because you are filtering on this filed anyway
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
SELECT a.part_no,
sum(a.BUY_QTY_DUE) AS Tot_Ord,
sum(nvl(b.REVISED_QTY_DUE,0)) AS S_O_Tot
FROM CUSTOMER_ORDER_JOIN a
LEFT OUTER JOIN SHOP_ORD b ON (a.part_no= b.part_no
AND b.contract = '20'
AND b.state = 'Planned')
WHERE a.PART_NO = '10002261'
AND a.OBJSTATE = 'Released'
AND a.CONTRACT = '10'
I have deleted grouping because you are filtering on this filed anyway
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
answered Nov 21 '18 at 14:44
evgeniya makarovaevgeniya makarova
11
11
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%2f53411348%2foracle-sql-results-duplicating-with-join%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
3
Provide sample data set as it would be beneficial for others to come up answers.
– Jåcob
Nov 21 '18 at 11:55