Avoid duplicate entries using full JOIN with SUM and GROUP BY
0
I am using HSQLDB for the database and have the following condition in which I have to avoid duplicate entries while joining 2 tables.
Table1
HMEXPENSE
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 100 | 2018-10-10 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-13 |
+--------+---------------+-------------+
Table2
HMINCOME
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 200 | 2018-10-10 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-12 |
+--------+---------------+-------------+
The current query which gives me the duplicate entries is as follows
SELECT e.expenseDate ,i.incomeDate , SUM(e.expenseAmount), SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate, i.incomeAmount, e.expenseAmount
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 200.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
And if I use this above-mentioned query to get the actual output which is required in my actual scenario is as follows
SELECT e.expenseDate, i.incomeDate , SUM(e.expenseAmount),SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 600.0 | 600.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
The requirement is to get the sum of amount for a single day and null entry for the date which is not present in another table.
Expected output is as follows
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 300.0 | 300.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
C3 and C4 column values are not calculated correctly due to the duplicate entries.
Help...
sql hsqldb
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
add a comment |
0
I am using HSQLDB for the database and have the following condition in which I have to avoid duplicate entries while joining 2 tables.
Table1
HMEXPENSE
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 100 | 2018-10-10 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-13 |
+--------+---------------+-------------+
Table2
HMINCOME
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 200 | 2018-10-10 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-12 |
+--------+---------------+-------------+
The current query which gives me the duplicate entries is as follows
SELECT e.expenseDate ,i.incomeDate , SUM(e.expenseAmount), SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate, i.incomeAmount, e.expenseAmount
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 200.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
And if I use this above-mentioned query to get the actual output which is required in my actual scenario is as follows
SELECT e.expenseDate, i.incomeDate , SUM(e.expenseAmount),SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 600.0 | 600.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
The requirement is to get the sum of amount for a single day and null entry for the date which is not present in another table.
Expected output is as follows
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 300.0 | 300.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
C3 and C4 column values are not calculated correctly due to the duplicate entries.
Help...
sql hsqldb
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. tryGROUP BY i.incomeDate, e.expenseDate.
– jarlh
Nov 25 '18 at 18:30
add a comment |
0
0
0
I am using HSQLDB for the database and have the following condition in which I have to avoid duplicate entries while joining 2 tables.
Table1
HMEXPENSE
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 100 | 2018-10-10 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-13 |
+--------+---------------+-------------+
Table2
HMINCOME
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 200 | 2018-10-10 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-12 |
+--------+---------------+-------------+
The current query which gives me the duplicate entries is as follows
SELECT e.expenseDate ,i.incomeDate , SUM(e.expenseAmount), SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate, i.incomeAmount, e.expenseAmount
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 200.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
And if I use this above-mentioned query to get the actual output which is required in my actual scenario is as follows
SELECT e.expenseDate, i.incomeDate , SUM(e.expenseAmount),SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 600.0 | 600.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
The requirement is to get the sum of amount for a single day and null entry for the date which is not present in another table.
Expected output is as follows
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 300.0 | 300.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
C3 and C4 column values are not calculated correctly due to the duplicate entries.
Help...
sql hsqldb
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
I am using HSQLDB for the database and have the following condition in which I have to avoid duplicate entries while joining 2 tables.
Table1
HMEXPENSE
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 100 | 2018-10-10 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-13 |
+--------+---------------+-------------+
Table2
HMINCOME
+--------+---------------+-------------+
| USERID | EXPENSEAMOUNT | EXPENSEDATE |
+--------+---------------+-------------+
| a | 200 | 2018-10-10 |
| a | 100 | 2018-10-11 |
| a | 200 | 2018-10-11 |
| a | 100 | 2018-10-12 |
+--------+---------------+-------------+
The current query which gives me the duplicate entries is as follows
SELECT e.expenseDate ,i.incomeDate , SUM(e.expenseAmount), SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate, i.incomeAmount, e.expenseAmount
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 100.0 |
| 2018-10-11 | 2018-10-11 | 200.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 100.0 | 200.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
And if I use this above-mentioned query to get the actual output which is required in my actual scenario is as follows
SELECT e.expenseDate, i.incomeDate , SUM(e.expenseAmount),SUM(i.incomeAmount)
FROM HMINCOME i FULL JOIN HMEXPENSE e on i.incomeDate = e.expenseDate
GROUP BY i.incomeDate,e.expenseDate
OUTPUT
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 600.0 | 600.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
The requirement is to get the sum of amount for a single day and null entry for the date which is not present in another table.
Expected output is as follows
+-------------+------------+-------+-------+
| EXPENSEDATE | INCOMEDATE | C3 | C4 |
+-------------+------------+-------+-------+
| 2018-10-10 | 2018-10-10 | 100.0 | 200.0 |
| 2018-10-11 | 2018-10-11 | 300.0 | 300.0 |
| <null> | 2018-10-12 | <null>| 100.0 |
| 2018-10-13 | <null> | 200.0 | <null>|
+-------------+------------+-------+-------+
C3 and C4 column values are not calculated correctly due to the duplicate entries.
Help...
sql hsqldb
sql hsqldb
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
edited Nov 25 '18 at 18:58
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
asked Nov 25 '18 at 18:25
Vaibhav KhandekarVaibhav Khandekar
33
33
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. tryGROUP BY i.incomeDate, e.expenseDate.
– jarlh
Nov 25 '18 at 18:30
add a comment |
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. tryGROUP BY i.incomeDate, e.expenseDate.
– jarlh
Nov 25 '18 at 18:30
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. try
GROUP BY i.incomeDate, e.expenseDate.– jarlh
Nov 25 '18 at 18:30
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. try
GROUP BY i.incomeDate, e.expenseDate.– jarlh
Nov 25 '18 at 18:30
add a comment |
3 Answers
3
active
oldest
votes
1
One method to solve this uses union all and group by:
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
add a comment |
1
Issue here is that you have multiple rows for a date within a table. So, we will need to aggregate them first inside a subquery. Afterwards, it will be then used to do FULL JOIN.
Try:
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
add a comment |
0
Thank you for your answers.
Both the answers posted worked for me.
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
And
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
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%2f53470556%2favoid-duplicate-entries-using-full-join-with-sum-and-group-by%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
1
One method to solve this uses union all and group by:
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
add a comment |
1
One method to solve this uses union all and group by:
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
add a comment |
1
1
1
One method to solve this uses union all and group by:
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
One method to solve this uses union all and group by:
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
answered Nov 25 '18 at 19:24
Gordon LinoffGordon Linoff
771k35304404
771k35304404
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
add a comment |
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
Thank you. This worked for me. Removed "ie" from the answer.
– Vaibhav Khandekar
Nov 26 '18 at 1:32
add a comment |
1
Issue here is that you have multiple rows for a date within a table. So, we will need to aggregate them first inside a subquery. Afterwards, it will be then used to do FULL JOIN.
Try:
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
add a comment |
1
Issue here is that you have multiple rows for a date within a table. So, we will need to aggregate them first inside a subquery. Afterwards, it will be then used to do FULL JOIN.
Try:
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
add a comment |
1
1
1
Issue here is that you have multiple rows for a date within a table. So, we will need to aggregate them first inside a subquery. Afterwards, it will be then used to do FULL JOIN.
Try:
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Issue here is that you have multiple rows for a date within a table. So, we will need to aggregate them first inside a subquery. Afterwards, it will be then used to do FULL JOIN.
Try:
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
edited Nov 26 '18 at 4:39
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 25 '18 at 18:45
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
add a comment |
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
The sub-queries' sum-columns need column aliases. No need for that outer GROUP BY.
– jarlh
Nov 25 '18 at 18:51
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
@jarlh thanks. fixed.
– Madhur Bhaiya
Nov 25 '18 at 18:53
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
The syntax is supported by HSQLDB
– fredt
Nov 26 '18 at 0:23
add a comment |
0
Thank you for your answers.
Both the answers posted worked for me.
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
And
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
add a comment |
0
Thank you for your answers.
Both the answers posted worked for me.
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
And
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
add a comment |
0
0
0
Thank you for your answers.
Both the answers posted worked for me.
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
And
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
Thank you for your answers.
Both the answers posted worked for me.
select dte, sum(incomeamount) as incomeamount, sum(expenseamount) as expenseamount
from ((select incomedate as dte, incomeamount, 0 as expenseamount
from hmincome
) union all
(select expensedate, 0, expenseAmount
from hmexpense
)
) ie
group by dte
order by dte;
And
SELECT
e.expenseDate,
i.incomeDate,
e.sumExpenseAmount,
i.sumIncomeAmount
FROM
(SELECT incomeDate, SUM(incomeAmount) sumIncomeAmount
FROM HMINCOME
GROUP BY incomeDate) i
FULL JOIN
(SELECT expenseDate, SUM(expenseAmount) sumExpenseAmount
FROM HMEXPENSE
GROUP BY expenseDate) e
ON i.incomeDate = e.expenseDate
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
answered Nov 26 '18 at 1:36
Vaibhav KhandekarVaibhav Khandekar
33
33
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%2f53470556%2favoid-duplicate-entries-using-full-join-with-sum-and-group-by%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
Please dont spam tag other RDBMS. Use the specific tag only.
– Madhur Bhaiya
Nov 25 '18 at 18:27
You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. try
GROUP BY i.incomeDate, e.expenseDate.– jarlh
Nov 25 '18 at 18:30