Create extra sum column using one of the table columns value
2
I have a table data like this:
Product Value
A 20
A 30
B 10
A 20
C 15
C 15
I need to get the sum based on the Value column like:
Product Value Sum
A 20 70
A 30 70
B 10 10
A 20 70
C 15 30
C 15 30
How do I query to create the sum of all A, B and C product still showing all records?
sql
sql-server tsql edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
add a comment |
2
I have a table data like this:
Product Value
A 20
A 30
B 10
A 20
C 15
C 15
I need to get the sum based on the Value column like:
Product Value Sum
A 20 70
A 30 70
B 10 10
A 20 70
C 15 30
C 15 30
How do I query to create the sum of all A, B and C product still showing all records?
sql
sql-server tsql edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
1
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57
add a comment |
2
2
2
I have a table data like this:
Product Value
A 20
A 30
B 10
A 20
C 15
C 15
I need to get the sum based on the Value column like:
Product Value Sum
A 20 70
A 30 70
B 10 10
A 20 70
C 15 30
C 15 30
How do I query to create the sum of all A, B and C product still showing all records?
sql
sql-server tsql edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
I have a table data like this:
Product Value
A 20
A 30
B 10
A 20
C 15
C 15
I need to get the sum based on the Value column like:
Product Value Sum
A 20 70
A 30 70
B 10 10
A 20 70
C 15 30
C 15 30
How do I query to create the sum of all A, B and C product still showing all records?
sql
sql-server tsql sql
sql-server tsql edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
edited Nov 15 '18 at 22:01
Rahul Neekhra
6001627
6001627
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
asked Nov 15 '18 at 17:50
Alex MartinezAlex Martinez
1788
1788
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
1
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57
add a comment |
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
1
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
1
1
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57
add a comment |
3 Answers
3
active
oldest
votes
4
You can achieve this using a window function for sum:
create table #totalValue
(
[Product] varchar(55),
Value int
)
insert into #totalValue
values
('A',20),
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)
select
Product,
[Value],
sum([Value]) over (partition by Product) as [Sum]
from #totalValue
This should scale better than a solution that queries the same table twice.
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
add a comment |
3
Please try with the below code snippet.
select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product
from producttable as c
group by c.product
) as b
on a.product = b.product
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
add a comment |
3
Here you go
CREATE TABLE T(
Product VARCHAR(20),
Value INT
);
INSERT INTO T VALUES
('A', 20),
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);
SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;
Returns:
+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A | 20 | 70 |
| A | 30 | 70 |
| B | 10 | 10 |
| A | 20 | 70 |
| C | 15 | 30 |
| C | 15 | 30 |
+---------+-------+-----+
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
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%2f53325269%2fcreate-extra-sum-column-using-one-of-the-table-columns-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
You can achieve this using a window function for sum:
create table #totalValue
(
[Product] varchar(55),
Value int
)
insert into #totalValue
values
('A',20),
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)
select
Product,
[Value],
sum([Value]) over (partition by Product) as [Sum]
from #totalValue
This should scale better than a solution that queries the same table twice.
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
add a comment |
4
You can achieve this using a window function for sum:
create table #totalValue
(
[Product] varchar(55),
Value int
)
insert into #totalValue
values
('A',20),
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)
select
Product,
[Value],
sum([Value]) over (partition by Product) as [Sum]
from #totalValue
This should scale better than a solution that queries the same table twice.
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
add a comment |
4
4
4
You can achieve this using a window function for sum:
create table #totalValue
(
[Product] varchar(55),
Value int
)
insert into #totalValue
values
('A',20),
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)
select
Product,
[Value],
sum([Value]) over (partition by Product) as [Sum]
from #totalValue
This should scale better than a solution that queries the same table twice.
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
You can achieve this using a window function for sum:
create table #totalValue
(
[Product] varchar(55),
Value int
)
insert into #totalValue
values
('A',20),
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)
select
Product,
[Value],
sum([Value]) over (partition by Product) as [Sum]
from #totalValue
This should scale better than a solution that queries the same table twice.
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
edited Nov 15 '18 at 18:04
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
answered Nov 15 '18 at 17:59
Kiplet GoodfriendKiplet Goodfriend
578916
578916
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
add a comment |
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
1
1
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
This is the solution I used. Works perfectly.
– Alex Martinez
Nov 15 '18 at 19:26
add a comment |
3
Please try with the below code snippet.
select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product
from producttable as c
group by c.product
) as b
on a.product = b.product
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
add a comment |
3
Please try with the below code snippet.
select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product
from producttable as c
group by c.product
) as b
on a.product = b.product
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
add a comment |
3
3
3
Please try with the below code snippet.
select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product
from producttable as c
group by c.product
) as b
on a.product = b.product
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
Please try with the below code snippet.
select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product
from producttable as c
group by c.product
) as b
on a.product = b.product
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
answered Nov 15 '18 at 17:56
Jayesh GoyaniJayesh Goyani
9,31141943
9,31141943
add a comment |
add a comment |
3
Here you go
CREATE TABLE T(
Product VARCHAR(20),
Value INT
);
INSERT INTO T VALUES
('A', 20),
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);
SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;
Returns:
+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A | 20 | 70 |
| A | 30 | 70 |
| B | 10 | 10 |
| A | 20 | 70 |
| C | 15 | 30 |
| C | 15 | 30 |
+---------+-------+-----+
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
add a comment |
3
Here you go
CREATE TABLE T(
Product VARCHAR(20),
Value INT
);
INSERT INTO T VALUES
('A', 20),
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);
SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;
Returns:
+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A | 20 | 70 |
| A | 30 | 70 |
| B | 10 | 10 |
| A | 20 | 70 |
| C | 15 | 30 |
| C | 15 | 30 |
+---------+-------+-----+
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
add a comment |
3
3
3
Here you go
CREATE TABLE T(
Product VARCHAR(20),
Value INT
);
INSERT INTO T VALUES
('A', 20),
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);
SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;
Returns:
+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A | 20 | 70 |
| A | 30 | 70 |
| B | 10 | 10 |
| A | 20 | 70 |
| C | 15 | 30 |
| C | 15 | 30 |
+---------+-------+-----+
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
Here you go
CREATE TABLE T(
Product VARCHAR(20),
Value INT
);
INSERT INTO T VALUES
('A', 20),
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);
SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;
Returns:
+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A | 20 | 70 |
| A | 30 | 70 |
| B | 10 | 10 |
| A | 20 | 70 |
| C | 15 | 30 |
| C | 15 | 30 |
+---------+-------+-----+
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
edited Nov 15 '18 at 18:06
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
answered Nov 15 '18 at 17:57
SamiSami
9,04831243
9,04831243
add a comment |
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%2f53325269%2fcreate-extra-sum-column-using-one-of-the-table-columns-value%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
StackOverflow is not a code or SQL writing service. We expect you to make an effort to solve the problem yourself first. Once you've done so and run into difficulties, you can explain what you're trying to do and the problem you're having, include the relevant portions of your code, and ask a specific question related to that code, and we'll be glad to try to help. New folks have a bit of a pass while being told this, but you've been on here long enough to know better.
– Utrolig
Nov 15 '18 at 18:22
1
@Utrolig The best way to explain it for me was this way. I actually have an Store Procedure but putting all that and explain it would bring confusion instead of making things easier. If I get to understand how to this, I can implemente a similar solution to my code.
– Alex Martinez
Nov 15 '18 at 18:57