Sum by groups between 2 tables
0
I, im trying to Sum by groups between 2 tables. Basically im trying to show only the Table 'Count' with each SUM() of Table 'MoneyMovements' group by 'Bank_code'. Is this possible?. I tryed with Left join (including the 'MoneyMovements'in 'Count'). but i dont understand how to separe the sum... Any suggestion?. Im using ACCESS 2007 in VB.NET
Table 'Count'
+----+--------------+
|Code|Bank |
+----+--------------+
|1 |MACRO |
+----+--------------+
|2 |Santender Rio |
+----+--------------+
|3 |Galicia |
+----+--------------+
Table 'MoneyMovements'
+-----+--------------+
|Money|Bank_code |
+-----+--------------+
|200 |1 |
+-----+--------------+
|300 |1 |
+-----+--------------+
|0 |2 |
+-----+--------------+
|500 |3 |
+-----+--------------+
|100 |3 |
+-----+--------------+
Response i Want:
+-----+--------------+
|Money|Bank |
+-----+--------------+
|500 |MACRO |
+-----+--------------+
|0 |Santender Rio |
+-----+--------------+
|600 |Galicia |
+-----+--------------+
sql ms-access
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
add a comment |
0
I, im trying to Sum by groups between 2 tables. Basically im trying to show only the Table 'Count' with each SUM() of Table 'MoneyMovements' group by 'Bank_code'. Is this possible?. I tryed with Left join (including the 'MoneyMovements'in 'Count'). but i dont understand how to separe the sum... Any suggestion?. Im using ACCESS 2007 in VB.NET
Table 'Count'
+----+--------------+
|Code|Bank |
+----+--------------+
|1 |MACRO |
+----+--------------+
|2 |Santender Rio |
+----+--------------+
|3 |Galicia |
+----+--------------+
Table 'MoneyMovements'
+-----+--------------+
|Money|Bank_code |
+-----+--------------+
|200 |1 |
+-----+--------------+
|300 |1 |
+-----+--------------+
|0 |2 |
+-----+--------------+
|500 |3 |
+-----+--------------+
|100 |3 |
+-----+--------------+
Response i Want:
+-----+--------------+
|Money|Bank |
+-----+--------------+
|500 |MACRO |
+-----+--------------+
|0 |Santender Rio |
+-----+--------------+
|600 |Galicia |
+-----+--------------+
sql ms-access
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
add a comment |
0
0
0
I, im trying to Sum by groups between 2 tables. Basically im trying to show only the Table 'Count' with each SUM() of Table 'MoneyMovements' group by 'Bank_code'. Is this possible?. I tryed with Left join (including the 'MoneyMovements'in 'Count'). but i dont understand how to separe the sum... Any suggestion?. Im using ACCESS 2007 in VB.NET
Table 'Count'
+----+--------------+
|Code|Bank |
+----+--------------+
|1 |MACRO |
+----+--------------+
|2 |Santender Rio |
+----+--------------+
|3 |Galicia |
+----+--------------+
Table 'MoneyMovements'
+-----+--------------+
|Money|Bank_code |
+-----+--------------+
|200 |1 |
+-----+--------------+
|300 |1 |
+-----+--------------+
|0 |2 |
+-----+--------------+
|500 |3 |
+-----+--------------+
|100 |3 |
+-----+--------------+
Response i Want:
+-----+--------------+
|Money|Bank |
+-----+--------------+
|500 |MACRO |
+-----+--------------+
|0 |Santender Rio |
+-----+--------------+
|600 |Galicia |
+-----+--------------+
sql ms-access
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
I, im trying to Sum by groups between 2 tables. Basically im trying to show only the Table 'Count' with each SUM() of Table 'MoneyMovements' group by 'Bank_code'. Is this possible?. I tryed with Left join (including the 'MoneyMovements'in 'Count'). but i dont understand how to separe the sum... Any suggestion?. Im using ACCESS 2007 in VB.NET
Table 'Count'
+----+--------------+
|Code|Bank |
+----+--------------+
|1 |MACRO |
+----+--------------+
|2 |Santender Rio |
+----+--------------+
|3 |Galicia |
+----+--------------+
Table 'MoneyMovements'
+-----+--------------+
|Money|Bank_code |
+-----+--------------+
|200 |1 |
+-----+--------------+
|300 |1 |
+-----+--------------+
|0 |2 |
+-----+--------------+
|500 |3 |
+-----+--------------+
|100 |3 |
+-----+--------------+
Response i Want:
+-----+--------------+
|Money|Bank |
+-----+--------------+
|500 |MACRO |
+-----+--------------+
|0 |Santender Rio |
+-----+--------------+
|600 |Galicia |
+-----+--------------+
sql ms-access
sql ms-access
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
asked Nov 16 '18 at 2:39
Roberto PeñaRoberto Peña
32
32
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
You need join with group by like:
Select c.Code, sum(m.Money) as Money
From Count c
inner join MoneyMovements m on c.Code = m.Bank_code
group by c.Code
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
I All after hours and hours i find it! this is:"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"
– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
add a comment |
0
Use "Left join" to get all the "Count" rows and Nz() to display 0 when there are no transactions in an account:
Select c.Code, Nz(sum(m.Money),0) as Money
From Count c
LEFT JOIN MoneyMovements m on c.Code = m.Bank_code
group by c.Code
Access really didn't like the table name "Count" so I used Counts instead, this is what I did:
SELECT Counts.code, Nz(Sum(MoneyMovements.Money),0) AS SumOfMoney
FROM Counts LEFT JOIN MoneyMovements ON Counts.Code = MoneyMovements.bank_code
GROUP BY Counts.code;
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
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%2f53330665%2fsum-by-groups-between-2-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You need join with group by like:
Select c.Code, sum(m.Money) as Money
From Count c
inner join MoneyMovements m on c.Code = m.Bank_code
group by c.Code
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
I All after hours and hours i find it! this is:"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"
– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
add a comment |
0
You need join with group by like:
Select c.Code, sum(m.Money) as Money
From Count c
inner join MoneyMovements m on c.Code = m.Bank_code
group by c.Code
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
I All after hours and hours i find it! this is:"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"
– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
add a comment |
0
0
0
You need join with group by like:
Select c.Code, sum(m.Money) as Money
From Count c
inner join MoneyMovements m on c.Code = m.Bank_code
group by c.Code
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
You need join with group by like:
Select c.Code, sum(m.Money) as Money
From Count c
inner join MoneyMovements m on c.Code = m.Bank_code
group by c.Code
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
answered Nov 16 '18 at 2:43
Eray BalkanliEray Balkanli
4,51652347
4,51652347
I All after hours and hours i find it! this is:"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"
– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
add a comment |
I All after hours and hours i find it! this is:"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"
– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
I All after hours and hours i find it! this is:
"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"– Roberto Peña
Nov 16 '18 at 3:01
I All after hours and hours i find it! this is:
"select p.Code,(SELECT SUM(b.Money) FROM MoneyMovements b WHERE b.Bank_code = p.Code) as SUM_TOTAL from Count p"– Roberto Peña
Nov 16 '18 at 3:01
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Ho thanks! i will test your solution now ! @Eray
– Roberto Peña
Nov 16 '18 at 3:04
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
Its works your code! i replace "inner join" by "Left join" to get all the table "Count" rows. Now, how to display a 0 when are no transactions in that acount?
– Roberto Peña
Nov 16 '18 at 3:10
add a comment |
0
Use "Left join" to get all the "Count" rows and Nz() to display 0 when there are no transactions in an account:
Select c.Code, Nz(sum(m.Money),0) as Money
From Count c
LEFT JOIN MoneyMovements m on c.Code = m.Bank_code
group by c.Code
Access really didn't like the table name "Count" so I used Counts instead, this is what I did:
SELECT Counts.code, Nz(Sum(MoneyMovements.Money),0) AS SumOfMoney
FROM Counts LEFT JOIN MoneyMovements ON Counts.Code = MoneyMovements.bank_code
GROUP BY Counts.code;
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
add a comment |
0
Use "Left join" to get all the "Count" rows and Nz() to display 0 when there are no transactions in an account:
Select c.Code, Nz(sum(m.Money),0) as Money
From Count c
LEFT JOIN MoneyMovements m on c.Code = m.Bank_code
group by c.Code
Access really didn't like the table name "Count" so I used Counts instead, this is what I did:
SELECT Counts.code, Nz(Sum(MoneyMovements.Money),0) AS SumOfMoney
FROM Counts LEFT JOIN MoneyMovements ON Counts.Code = MoneyMovements.bank_code
GROUP BY Counts.code;
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
add a comment |
0
0
0
Use "Left join" to get all the "Count" rows and Nz() to display 0 when there are no transactions in an account:
Select c.Code, Nz(sum(m.Money),0) as Money
From Count c
LEFT JOIN MoneyMovements m on c.Code = m.Bank_code
group by c.Code
Access really didn't like the table name "Count" so I used Counts instead, this is what I did:
SELECT Counts.code, Nz(Sum(MoneyMovements.Money),0) AS SumOfMoney
FROM Counts LEFT JOIN MoneyMovements ON Counts.Code = MoneyMovements.bank_code
GROUP BY Counts.code;
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
Use "Left join" to get all the "Count" rows and Nz() to display 0 when there are no transactions in an account:
Select c.Code, Nz(sum(m.Money),0) as Money
From Count c
LEFT JOIN MoneyMovements m on c.Code = m.Bank_code
group by c.Code
Access really didn't like the table name "Count" so I used Counts instead, this is what I did:
SELECT Counts.code, Nz(Sum(MoneyMovements.Money),0) AS SumOfMoney
FROM Counts LEFT JOIN MoneyMovements ON Counts.Code = MoneyMovements.bank_code
GROUP BY Counts.code;
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
edited Nov 16 '18 at 23:39
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
answered Nov 16 '18 at 3:50
Used_By_AlreadyUsed_By_Already
23.1k22139
23.1k22139
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
add a comment |
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
its strange... this return me anything... This Works: 'select c.Bank,(SELECT SUM(m.Money) FROM MoneyMovements m WHERE m.Bank_code = c.Code) as Total_money from Count c' Why this works and your not? im confused. first time i wrote something like you but doesnt work, after 6 hours i tried this way. therefore i tryed "Nz" function in my code but doesnt work. I use Microsoft.ACE.OLEDB.12.0 and VB.NET
– Roberto Peña
Nov 16 '18 at 8:28
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
@RobertoPeña please note I added to my answer with an example built in Access
– Used_By_Already
Nov 16 '18 at 23:40
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%2f53330665%2fsum-by-groups-between-2-tables%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
