A SQL using Sum and Group By for multiple tables with different primary keys
0
Let’s say I have following tables: inv_mast, po_hdr, po_line and vessel_receipts_line
And I have following data:
inv_mast.inv_mast_uid
12
2852
po_hdr.po_no
1000019
1000118
po_line.po_no|line_id|po_line_uid|inv_mast_uid
1000019 |1 |234 |12
1000118 |1 |646 |2852
1000118 |2 |648 |2852
vessel_receipts_line.po_line_uid|container_qty_received|row_status_flag
234 |9700 |702
646 |320000 |702
646 |2710000 |701
648 |3250000 |702
Now I’m trying to sum the container_qty_received for each po_no + inv_mast_uid + row_status_flag, which should be 702.
For example, the result I expected is
po_no |inv_mast_uid|container_qty_received|row_status_flag
1000019|12 |9700 |702
1000118|2852 |3570000 |702
1000118|2852 |2710000 |701
I have tried several different codes, with left join, or with subquery, none of them work. The two quantities just don't sum.
The result now is:
po_no |inv_mast_uid|container_qty_received
1000019|12 |9700
1000118|2852 |320000
1000118|2852 |3250000
Here is one of my attempts.
SELECT a.inv_mast_uid
, b.qty_in_vessel as qty_in_vessel
, c.po_no
FROM po_line a
LEFT JOIN
po_hdr AS c ON a.po_no = c.po_no
LEFT JOIN
(
SELECT vessel_receipts_line.po_line_uid, SUM(vessel_receipts_line.container_qty_received) AS qty_in_vessel
FROM vessel_receipts_line
WHERE vessel_receipts_line.row_status_flag = 702
GROUP BY
vessel_receipts_line.po_line_uid
) AS b
ON b.po_line_uid = a.po_line_uid
GROUP BY
a.inv_mast_uid, b.qty_in_vessel, c.po_no
ORDER BY c.po_no
Please help!
sql sql-server-2014
asked Jan 1 at 3:33
LeonLeon
64
add a comment |
0
Let’s say I have following tables: inv_mast, po_hdr, po_line and vessel_receipts_line
And I have following data:
inv_mast.inv_mast_uid
12
2852
po_hdr.po_no
1000019
1000118
po_line.po_no|line_id|po_line_uid|inv_mast_uid
1000019 |1 |234 |12
1000118 |1 |646 |2852
1000118 |2 |648 |2852
vessel_receipts_line.po_line_uid|container_qty_received|row_status_flag
234 |9700 |702
646 |320000 |702
646 |2710000 |701
648 |3250000 |702
Now I’m trying to sum the container_qty_received for each po_no + inv_mast_uid + row_status_flag, which should be 702.
For example, the result I expected is
po_no |inv_mast_uid|container_qty_received|row_status_flag
1000019|12 |9700 |702
1000118|2852 |3570000 |702
1000118|2852 |2710000 |701
I have tried several different codes, with left join, or with subquery, none of them work. The two quantities just don't sum.
The result now is:
po_no |inv_mast_uid|container_qty_received
1000019|12 |9700
1000118|2852 |320000
1000118|2852 |3250000
Here is one of my attempts.
SELECT a.inv_mast_uid
, b.qty_in_vessel as qty_in_vessel
, c.po_no
FROM po_line a
LEFT JOIN
po_hdr AS c ON a.po_no = c.po_no
LEFT JOIN
(
SELECT vessel_receipts_line.po_line_uid, SUM(vessel_receipts_line.container_qty_received) AS qty_in_vessel
FROM vessel_receipts_line
WHERE vessel_receipts_line.row_status_flag = 702
GROUP BY
vessel_receipts_line.po_line_uid
) AS b
ON b.po_line_uid = a.po_line_uid
GROUP BY
a.inv_mast_uid, b.qty_in_vessel, c.po_no
ORDER BY c.po_no
Please help!
sql sql-server-2014
asked Jan 1 at 3:33
LeonLeon
64
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31
add a comment |
0
0
0
Let’s say I have following tables: inv_mast, po_hdr, po_line and vessel_receipts_line
And I have following data:
inv_mast.inv_mast_uid
12
2852
po_hdr.po_no
1000019
1000118
po_line.po_no|line_id|po_line_uid|inv_mast_uid
1000019 |1 |234 |12
1000118 |1 |646 |2852
1000118 |2 |648 |2852
vessel_receipts_line.po_line_uid|container_qty_received|row_status_flag
234 |9700 |702
646 |320000 |702
646 |2710000 |701
648 |3250000 |702
Now I’m trying to sum the container_qty_received for each po_no + inv_mast_uid + row_status_flag, which should be 702.
For example, the result I expected is
po_no |inv_mast_uid|container_qty_received|row_status_flag
1000019|12 |9700 |702
1000118|2852 |3570000 |702
1000118|2852 |2710000 |701
I have tried several different codes, with left join, or with subquery, none of them work. The two quantities just don't sum.
The result now is:
po_no |inv_mast_uid|container_qty_received
1000019|12 |9700
1000118|2852 |320000
1000118|2852 |3250000
Here is one of my attempts.
SELECT a.inv_mast_uid
, b.qty_in_vessel as qty_in_vessel
, c.po_no
FROM po_line a
LEFT JOIN
po_hdr AS c ON a.po_no = c.po_no
LEFT JOIN
(
SELECT vessel_receipts_line.po_line_uid, SUM(vessel_receipts_line.container_qty_received) AS qty_in_vessel
FROM vessel_receipts_line
WHERE vessel_receipts_line.row_status_flag = 702
GROUP BY
vessel_receipts_line.po_line_uid
) AS b
ON b.po_line_uid = a.po_line_uid
GROUP BY
a.inv_mast_uid, b.qty_in_vessel, c.po_no
ORDER BY c.po_no
Please help!
sql sql-server-2014
asked Jan 1 at 3:33
LeonLeon
64
Let’s say I have following tables: inv_mast, po_hdr, po_line and vessel_receipts_line
And I have following data:
inv_mast.inv_mast_uid
12
2852
po_hdr.po_no
1000019
1000118
po_line.po_no|line_id|po_line_uid|inv_mast_uid
1000019 |1 |234 |12
1000118 |1 |646 |2852
1000118 |2 |648 |2852
vessel_receipts_line.po_line_uid|container_qty_received|row_status_flag
234 |9700 |702
646 |320000 |702
646 |2710000 |701
648 |3250000 |702
Now I’m trying to sum the container_qty_received for each po_no + inv_mast_uid + row_status_flag, which should be 702.
For example, the result I expected is
po_no |inv_mast_uid|container_qty_received|row_status_flag
1000019|12 |9700 |702
1000118|2852 |3570000 |702
1000118|2852 |2710000 |701
I have tried several different codes, with left join, or with subquery, none of them work. The two quantities just don't sum.
The result now is:
po_no |inv_mast_uid|container_qty_received
1000019|12 |9700
1000118|2852 |320000
1000118|2852 |3250000
Here is one of my attempts.
SELECT a.inv_mast_uid
, b.qty_in_vessel as qty_in_vessel
, c.po_no
FROM po_line a
LEFT JOIN
po_hdr AS c ON a.po_no = c.po_no
LEFT JOIN
(
SELECT vessel_receipts_line.po_line_uid, SUM(vessel_receipts_line.container_qty_received) AS qty_in_vessel
FROM vessel_receipts_line
WHERE vessel_receipts_line.row_status_flag = 702
GROUP BY
vessel_receipts_line.po_line_uid
) AS b
ON b.po_line_uid = a.po_line_uid
GROUP BY
a.inv_mast_uid, b.qty_in_vessel, c.po_no
ORDER BY c.po_no
Please help!
sql sql-server-2014
sql sql-server-2014
asked Jan 1 at 3:33
LeonLeon
64
asked Jan 1 at 3:33
LeonLeon
64
edited Jan 1 at 5:17
Leon
asked Jan 1 at 3:33
LeonLeon
64
asked Jan 1 at 3:33
LeonLeon
64
asked Jan 1 at 3:33
LeonLeon
64
64
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31
add a comment |
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31
add a comment |
1 Answer
1
active
oldest
votes
0
Join po_line and vessel_receipts_line on po_line_uid, group by po_no and inv_mast_uid and sum the container_qty_received.
SELECT pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag,
sum(vrl.container_qty_received) container_qty_received
FROM po_line pl
INNER JOIN vessel_receipts_line vrl
ON vrl.po_line_uid = pl.po_line_uid
GROUP BY pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag;
db<>fiddle
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
|
show 2 more comments
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%2f53992862%2fa-sql-using-sum-and-group-by-for-multiple-tables-with-different-primary-keys%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
Join po_line and vessel_receipts_line on po_line_uid, group by po_no and inv_mast_uid and sum the container_qty_received.
SELECT pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag,
sum(vrl.container_qty_received) container_qty_received
FROM po_line pl
INNER JOIN vessel_receipts_line vrl
ON vrl.po_line_uid = pl.po_line_uid
GROUP BY pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag;
db<>fiddle
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
|
show 2 more comments
0
Join po_line and vessel_receipts_line on po_line_uid, group by po_no and inv_mast_uid and sum the container_qty_received.
SELECT pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag,
sum(vrl.container_qty_received) container_qty_received
FROM po_line pl
INNER JOIN vessel_receipts_line vrl
ON vrl.po_line_uid = pl.po_line_uid
GROUP BY pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag;
db<>fiddle
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
|
show 2 more comments
0
0
0
Join po_line and vessel_receipts_line on po_line_uid, group by po_no and inv_mast_uid and sum the container_qty_received.
SELECT pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag,
sum(vrl.container_qty_received) container_qty_received
FROM po_line pl
INNER JOIN vessel_receipts_line vrl
ON vrl.po_line_uid = pl.po_line_uid
GROUP BY pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag;
db<>fiddle
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
Join po_line and vessel_receipts_line on po_line_uid, group by po_no and inv_mast_uid and sum the container_qty_received.
SELECT pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag,
sum(vrl.container_qty_received) container_qty_received
FROM po_line pl
INNER JOIN vessel_receipts_line vrl
ON vrl.po_line_uid = pl.po_line_uid
GROUP BY pl.po_no,
pl.inv_mast_uid,
vrl.row_status_flag;
db<>fiddle
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
edited Jan 1 at 5:27
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
answered Jan 1 at 3:41
sticky bitsticky bit
15.1k101632
15.1k101632
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
|
show 2 more comments
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
The result is still wrong. It shows 6280000 for po_no 1000118 and inv_mast_uid 2852. I think some of them have been summed twice.
– Leon
Jan 1 at 4:11
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
@Leon: It does work in terms of producing your desired result with the given sample data: dbfiddle.uk/…
– sticky bit
Jan 1 at 4:19
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
Trust me, your solution is the same as my first attempt. It will double the quantity. That's why I use subquery here. Thanks though, you are really prompt.
– Leon
Jan 1 at 4:24
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
@Leon: Have you looked at the fiddle? It doesn't double anything. It returns 3570000 for 1000118 / 2852, which is what you wrote you'd expect.
– sticky bit
Jan 1 at 4:27
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
OK. Let me check the data. Maybe I missed something. Sorry, it's not what I want.
– Leon
Jan 1 at 4:48
|
show 2 more comments
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%2f53992862%2fa-sql-using-sum-and-group-by-for-multiple-tables-with-different-primary-keys%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
Hey, can you please elaborate your goal?
– Rakib
Jan 1 at 6:31