Return sum of matching rows as a separate field
-1
I want to write a SQL query that will give me field C as the sum of field B for each unique row in field A. So for X, field B has 10 and 35, therefore field C would should 45 for both rows of X.
I need a query that will return the entire result as one "dataset" like below (instead of a query that just gives me field C). I think maybe I would need 2 queries? One to grab A and B results and then a 2nd query to select from results and SUM(expression) for field C?
sql
sql-server asked Nov 21 '18 at 23:40
goosemangooseman
1651211
add a comment |
-1
I want to write a SQL query that will give me field C as the sum of field B for each unique row in field A. So for X, field B has 10 and 35, therefore field C would should 45 for both rows of X.
I need a query that will return the entire result as one "dataset" like below (instead of a query that just gives me field C). I think maybe I would need 2 queries? One to grab A and B results and then a 2nd query to select from results and SUM(expression) for field C?
sql
sql-server asked Nov 21 '18 at 23:40
goosemangooseman
1651211
2
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08
add a comment |
-1
-1
-1
I want to write a SQL query that will give me field C as the sum of field B for each unique row in field A. So for X, field B has 10 and 35, therefore field C would should 45 for both rows of X.
I need a query that will return the entire result as one "dataset" like below (instead of a query that just gives me field C). I think maybe I would need 2 queries? One to grab A and B results and then a 2nd query to select from results and SUM(expression) for field C?
sql
sql-server asked Nov 21 '18 at 23:40
goosemangooseman
1651211
I want to write a SQL query that will give me field C as the sum of field B for each unique row in field A. So for X, field B has 10 and 35, therefore field C would should 45 for both rows of X.
I need a query that will return the entire result as one "dataset" like below (instead of a query that just gives me field C). I think maybe I would need 2 queries? One to grab A and B results and then a 2nd query to select from results and SUM(expression) for field C?
sql
sql-server sql
sql-server asked Nov 21 '18 at 23:40
goosemangooseman
1651211
asked Nov 21 '18 at 23:40
goosemangooseman
1651211
asked Nov 21 '18 at 23:40
goosemangooseman
1651211
asked Nov 21 '18 at 23:40
goosemangooseman
1651211
asked Nov 21 '18 at 23:40
goosemangooseman
1651211
1651211
2
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08
add a comment |
2
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08
2
2
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08
add a comment |
3 Answers
3
active
oldest
votes
4
You can use a window function SUM()OVER() or a SUM() within a subquery like
CREATE TABLE T(
A VARCHAR(10),
B INT
);
INSERT INTO T VALUES
('X', 10),
('Y', 15),
('Z', 40),
('X', 35),
('Y', 10);
SELECT *,
(SELECT SUM(B) FROM T WHERE A = TT.A) C,
SUM(B) OVER(PARTITION BY A) AnotherC
FROM T TT;
Here is a live demo
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
add a comment |
3
Looks like Sami posted while I was doing my copy/paste :) but here's a similar solution:
Declare @test Table
(
a varchar(10),
b int
)
Insert Into @test (a, b) Values ('X', 10);
Insert Into @test (a, b) Values ('Y', 15);
Insert Into @test (a, b) Values ('Z', 40);
Insert Into @test (a, b) Values ('X', 35);
Insert Into @test (a, b) Values ('Y', 10);
Select t.a, t.b, (Select sum(sq.b) From @test sq Where sq.a = t.a) as c
From @test t
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
add a comment |
2
The below given query is enough.
select A,B, SUM(B) OVER(PARTITION BY A) AS C FROM TABLE
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
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%2f53421979%2freturn-sum-of-matching-rows-as-a-separate-field%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 use a window function SUM()OVER() or a SUM() within a subquery like
CREATE TABLE T(
A VARCHAR(10),
B INT
);
INSERT INTO T VALUES
('X', 10),
('Y', 15),
('Z', 40),
('X', 35),
('Y', 10);
SELECT *,
(SELECT SUM(B) FROM T WHERE A = TT.A) C,
SUM(B) OVER(PARTITION BY A) AnotherC
FROM T TT;
Here is a live demo
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
add a comment |
4
You can use a window function SUM()OVER() or a SUM() within a subquery like
CREATE TABLE T(
A VARCHAR(10),
B INT
);
INSERT INTO T VALUES
('X', 10),
('Y', 15),
('Z', 40),
('X', 35),
('Y', 10);
SELECT *,
(SELECT SUM(B) FROM T WHERE A = TT.A) C,
SUM(B) OVER(PARTITION BY A) AnotherC
FROM T TT;
Here is a live demo
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
add a comment |
4
4
4
You can use a window function SUM()OVER() or a SUM() within a subquery like
CREATE TABLE T(
A VARCHAR(10),
B INT
);
INSERT INTO T VALUES
('X', 10),
('Y', 15),
('Z', 40),
('X', 35),
('Y', 10);
SELECT *,
(SELECT SUM(B) FROM T WHERE A = TT.A) C,
SUM(B) OVER(PARTITION BY A) AnotherC
FROM T TT;
Here is a live demo
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
You can use a window function SUM()OVER() or a SUM() within a subquery like
CREATE TABLE T(
A VARCHAR(10),
B INT
);
INSERT INTO T VALUES
('X', 10),
('Y', 15),
('Z', 40),
('X', 35),
('Y', 10);
SELECT *,
(SELECT SUM(B) FROM T WHERE A = TT.A) C,
SUM(B) OVER(PARTITION BY A) AnotherC
FROM T TT;
Here is a live demo
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
edited Nov 21 '18 at 23:55
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
answered Nov 21 '18 at 23:48
SamiSami
9,28331244
9,28331244
add a comment |
add a comment |
3
Looks like Sami posted while I was doing my copy/paste :) but here's a similar solution:
Declare @test Table
(
a varchar(10),
b int
)
Insert Into @test (a, b) Values ('X', 10);
Insert Into @test (a, b) Values ('Y', 15);
Insert Into @test (a, b) Values ('Z', 40);
Insert Into @test (a, b) Values ('X', 35);
Insert Into @test (a, b) Values ('Y', 10);
Select t.a, t.b, (Select sum(sq.b) From @test sq Where sq.a = t.a) as c
From @test t
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
add a comment |
3
Looks like Sami posted while I was doing my copy/paste :) but here's a similar solution:
Declare @test Table
(
a varchar(10),
b int
)
Insert Into @test (a, b) Values ('X', 10);
Insert Into @test (a, b) Values ('Y', 15);
Insert Into @test (a, b) Values ('Z', 40);
Insert Into @test (a, b) Values ('X', 35);
Insert Into @test (a, b) Values ('Y', 10);
Select t.a, t.b, (Select sum(sq.b) From @test sq Where sq.a = t.a) as c
From @test t
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
add a comment |
3
3
3
Looks like Sami posted while I was doing my copy/paste :) but here's a similar solution:
Declare @test Table
(
a varchar(10),
b int
)
Insert Into @test (a, b) Values ('X', 10);
Insert Into @test (a, b) Values ('Y', 15);
Insert Into @test (a, b) Values ('Z', 40);
Insert Into @test (a, b) Values ('X', 35);
Insert Into @test (a, b) Values ('Y', 10);
Select t.a, t.b, (Select sum(sq.b) From @test sq Where sq.a = t.a) as c
From @test t
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
Looks like Sami posted while I was doing my copy/paste :) but here's a similar solution:
Declare @test Table
(
a varchar(10),
b int
)
Insert Into @test (a, b) Values ('X', 10);
Insert Into @test (a, b) Values ('Y', 15);
Insert Into @test (a, b) Values ('Z', 40);
Insert Into @test (a, b) Values ('X', 35);
Insert Into @test (a, b) Values ('Y', 10);
Select t.a, t.b, (Select sum(sq.b) From @test sq Where sq.a = t.a) as c
From @test t
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
answered Nov 21 '18 at 23:59
Jon VoteJon Vote
38710
38710
add a comment |
add a comment |
2
The below given query is enough.
select A,B, SUM(B) OVER(PARTITION BY A) AS C FROM TABLE
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
add a comment |
2
The below given query is enough.
select A,B, SUM(B) OVER(PARTITION BY A) AS C FROM TABLE
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
add a comment |
2
2
2
The below given query is enough.
select A,B, SUM(B) OVER(PARTITION BY A) AS C FROM TABLE
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
The below given query is enough.
select A,B, SUM(B) OVER(PARTITION BY A) AS C FROM TABLE
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
edited Nov 22 '18 at 14:38
Madhur Bhaiya
19.6k62336
19.6k62336
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
answered Nov 22 '18 at 6:53
Vivek KhandelwalVivek Khandelwal
1246
1246
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
add a comment |
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
1
1
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
Like I see that 13 hours before?
– Sami
Nov 22 '18 at 13:15
2
2
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
Why this can get vote up? It's just same answer..
– dwir182
Nov 22 '18 at 13:34
1
1
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
What purpose does a duplicate answer serve ?
– Madhur Bhaiya
Nov 22 '18 at 13:35
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%2f53421979%2freturn-sum-of-matching-rows-as-a-separate-field%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
2
a window function with Partition By A and SUM B would work I think
– Dawood Awan
Nov 21 '18 at 23:43
If you want to pass you gotta go to class. Don't skip and then dump homework problems here. Also see How do I ask homework questions on Stack Overflow. You are expected to make an effort.
– jww
Nov 23 '18 at 17:40
I know it sounds like a homework question when I was writing the question but I can assure you it's not. Real world problem here. I just wrote it in simpler terms to make the problem more visible instead of writing a bunch of SQL. I also wrote a suggestion to solving the problem, albeit vague but still a suggestion.
– gooseman
Nov 24 '18 at 15:08