Add column field values in the same row?
Im working with SQL on db2.
Shipping table:
+--------+------+
| Weight | Cost |
+--------+------+
| 2 | 5 |
| 5 | 10 |
| 10 | 15 |
| 30 | 45 |
| 50 | 80 |
+--------+------+
Item table:
+---------+--------+
| Item ID | Weight |
+---------+--------+
| 1 | 34 |
| 2 | 4 |
| 3 | 9 |
| 4 | 5 |
| 5 | 16 |
+---------+--------+
I want to associate the appropriate shipping cost to the Item using the weight. So if the weight of the item would associate to the shipping weight equal to that or the next weight greater than that in the shipping weight table.
What I want my resulting table to be after INNER JOIN and everything is:
+---------+-------------+-----------------+---------------+
| Item ID | Item Weight | Shipping Weight | Shipping Cost |
+---------+-------------+-----------------+---------------+
| 1 | 34 | 50 | 80 |
| 2 | 4 | 5 | 10 |
| 3 | 9 | 10 | 15 |
| 4 | 5 | 5 | 10 |
| 5 | 16 | 30 | 45 |
+---------+-------------+-----------------+---------------+
I am unable to figure out how to associate the shipping weight to the item weight. Once I do that I can join the shipping cost.
What I tried was to use "WHERE >= Shipping Weight" but that gives me all the possibilities, and I just want the best one which is either equal than or next greatest. Im not good at explaining this but I hope you understand what I mean by looking at the resulting table I want.
Thanks!
sql database db2
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
add a comment |
Im working with SQL on db2.
Shipping table:
+--------+------+
| Weight | Cost |
+--------+------+
| 2 | 5 |
| 5 | 10 |
| 10 | 15 |
| 30 | 45 |
| 50 | 80 |
+--------+------+
Item table:
+---------+--------+
| Item ID | Weight |
+---------+--------+
| 1 | 34 |
| 2 | 4 |
| 3 | 9 |
| 4 | 5 |
| 5 | 16 |
+---------+--------+
I want to associate the appropriate shipping cost to the Item using the weight. So if the weight of the item would associate to the shipping weight equal to that or the next weight greater than that in the shipping weight table.
What I want my resulting table to be after INNER JOIN and everything is:
+---------+-------------+-----------------+---------------+
| Item ID | Item Weight | Shipping Weight | Shipping Cost |
+---------+-------------+-----------------+---------------+
| 1 | 34 | 50 | 80 |
| 2 | 4 | 5 | 10 |
| 3 | 9 | 10 | 15 |
| 4 | 5 | 5 | 10 |
| 5 | 16 | 30 | 45 |
+---------+-------------+-----------------+---------------+
I am unable to figure out how to associate the shipping weight to the item weight. Once I do that I can join the shipping cost.
What I tried was to use "WHERE >= Shipping Weight" but that gives me all the possibilities, and I just want the best one which is either equal than or next greatest. Im not good at explaining this but I hope you understand what I mean by looking at the resulting table I want.
Thanks!
sql database db2
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,lower <= actual weight < upper- the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs30.00001- it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.
– Clockwork-Muse
Nov 17 '18 at 6:08
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15
add a comment |
Im working with SQL on db2.
Shipping table:
+--------+------+
| Weight | Cost |
+--------+------+
| 2 | 5 |
| 5 | 10 |
| 10 | 15 |
| 30 | 45 |
| 50 | 80 |
+--------+------+
Item table:
+---------+--------+
| Item ID | Weight |
+---------+--------+
| 1 | 34 |
| 2 | 4 |
| 3 | 9 |
| 4 | 5 |
| 5 | 16 |
+---------+--------+
I want to associate the appropriate shipping cost to the Item using the weight. So if the weight of the item would associate to the shipping weight equal to that or the next weight greater than that in the shipping weight table.
What I want my resulting table to be after INNER JOIN and everything is:
+---------+-------------+-----------------+---------------+
| Item ID | Item Weight | Shipping Weight | Shipping Cost |
+---------+-------------+-----------------+---------------+
| 1 | 34 | 50 | 80 |
| 2 | 4 | 5 | 10 |
| 3 | 9 | 10 | 15 |
| 4 | 5 | 5 | 10 |
| 5 | 16 | 30 | 45 |
+---------+-------------+-----------------+---------------+
I am unable to figure out how to associate the shipping weight to the item weight. Once I do that I can join the shipping cost.
What I tried was to use "WHERE >= Shipping Weight" but that gives me all the possibilities, and I just want the best one which is either equal than or next greatest. Im not good at explaining this but I hope you understand what I mean by looking at the resulting table I want.
Thanks!
sql database db2
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
Im working with SQL on db2.
Shipping table:
+--------+------+
| Weight | Cost |
+--------+------+
| 2 | 5 |
| 5 | 10 |
| 10 | 15 |
| 30 | 45 |
| 50 | 80 |
+--------+------+
Item table:
+---------+--------+
| Item ID | Weight |
+---------+--------+
| 1 | 34 |
| 2 | 4 |
| 3 | 9 |
| 4 | 5 |
| 5 | 16 |
+---------+--------+
I want to associate the appropriate shipping cost to the Item using the weight. So if the weight of the item would associate to the shipping weight equal to that or the next weight greater than that in the shipping weight table.
What I want my resulting table to be after INNER JOIN and everything is:
+---------+-------------+-----------------+---------------+
| Item ID | Item Weight | Shipping Weight | Shipping Cost |
+---------+-------------+-----------------+---------------+
| 1 | 34 | 50 | 80 |
| 2 | 4 | 5 | 10 |
| 3 | 9 | 10 | 15 |
| 4 | 5 | 5 | 10 |
| 5 | 16 | 30 | 45 |
+---------+-------------+-----------------+---------------+
I am unable to figure out how to associate the shipping weight to the item weight. Once I do that I can join the shipping cost.
What I tried was to use "WHERE >= Shipping Weight" but that gives me all the possibilities, and I just want the best one which is either equal than or next greatest. Im not good at explaining this but I hope you understand what I mean by looking at the resulting table I want.
Thanks!
sql database db2
sql database db2
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
asked Nov 16 '18 at 21:36
sohaib-asimsohaib-asim
134
134
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,lower <= actual weight < upper- the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs30.00001- it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.
– Clockwork-Muse
Nov 17 '18 at 6:08
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15
add a comment |
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,lower <= actual weight < upper- the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs30.00001- it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.
– Clockwork-Muse
Nov 17 '18 at 6:08
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,
lower <= actual weight < upper - the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs 30.00001 - it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.– Clockwork-Muse
Nov 17 '18 at 6:08
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,
lower <= actual weight < upper - the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs 30.00001 - it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.– Clockwork-Muse
Nov 17 '18 at 6:08
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15
add a comment |
1 Answer
1
active
oldest
votes
Well, you can use a correlated subquery to get the weight and then a join to get the associated cost:
select i.*, s.*
from (select i.*,
(select min(s.weight)
from shipping s
where s.weight >= i.weight
) as shipping_weight
from items i
) i join
shipping s
on i.shipping_weight = s.weight;
Perhaps a funner method uses window functions:
select i.*, s.*
from items i join
(select s.*, lag(weight) over (order by weight) as prev_weight
from shipping s
) s
on i.weight <= s.weight and
(i.weight > prev_weight or prev_weight is null)
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21: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
});
}
});
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%2f53345750%2fadd-column-field-values-in-the-same-row%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
Well, you can use a correlated subquery to get the weight and then a join to get the associated cost:
select i.*, s.*
from (select i.*,
(select min(s.weight)
from shipping s
where s.weight >= i.weight
) as shipping_weight
from items i
) i join
shipping s
on i.shipping_weight = s.weight;
Perhaps a funner method uses window functions:
select i.*, s.*
from items i join
(select s.*, lag(weight) over (order by weight) as prev_weight
from shipping s
) s
on i.weight <= s.weight and
(i.weight > prev_weight or prev_weight is null)
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
add a comment |
Well, you can use a correlated subquery to get the weight and then a join to get the associated cost:
select i.*, s.*
from (select i.*,
(select min(s.weight)
from shipping s
where s.weight >= i.weight
) as shipping_weight
from items i
) i join
shipping s
on i.shipping_weight = s.weight;
Perhaps a funner method uses window functions:
select i.*, s.*
from items i join
(select s.*, lag(weight) over (order by weight) as prev_weight
from shipping s
) s
on i.weight <= s.weight and
(i.weight > prev_weight or prev_weight is null)
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
add a comment |
Well, you can use a correlated subquery to get the weight and then a join to get the associated cost:
select i.*, s.*
from (select i.*,
(select min(s.weight)
from shipping s
where s.weight >= i.weight
) as shipping_weight
from items i
) i join
shipping s
on i.shipping_weight = s.weight;
Perhaps a funner method uses window functions:
select i.*, s.*
from items i join
(select s.*, lag(weight) over (order by weight) as prev_weight
from shipping s
) s
on i.weight <= s.weight and
(i.weight > prev_weight or prev_weight is null)
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
Well, you can use a correlated subquery to get the weight and then a join to get the associated cost:
select i.*, s.*
from (select i.*,
(select min(s.weight)
from shipping s
where s.weight >= i.weight
) as shipping_weight
from items i
) i join
shipping s
on i.shipping_weight = s.weight;
Perhaps a funner method uses window functions:
select i.*, s.*
from items i join
(select s.*, lag(weight) over (order by weight) as prev_weight
from shipping s
) s
on i.weight <= s.weight and
(i.weight > prev_weight or prev_weight is null)
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
answered Nov 16 '18 at 21:39
Gordon LinoffGordon Linoff
769k35302403
769k35302403
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
add a comment |
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
Thanks a lot. The "MIN(s.weight)" and "WHERE s.weight >= i.weight" part is what i needed. Cheers!
– sohaib-asim
Nov 16 '18 at 21:54
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%2f53345750%2fadd-column-field-values-in-the-same-row%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
Side note: My usual recommendation for positive numbers is "lower-bound inclusive, upper-bound exclusive" (that is,
lower <= actual weight < upper- the opposite of what you show here). The reason for this is dealing with the range of all numbers: consider what happens if any item weighs30.00001- it becomes much easier to reason about (you're currently storing the upper bound - why does the top one get carried over?)/query, and to write the legal copy for. In that scenario, you'd store the lower bound, meaning the top rate is just part of the normal last rate.– Clockwork-Muse
Nov 17 '18 at 6:08
Also, regardless of which direction you store the bound, for ease of use I recommend creating a view or materialized query table (which might help with speed, too, although the source table is likely small enough it shouldn't matter ).
– Clockwork-Muse
Nov 17 '18 at 6:15