• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

SQL Subtracting SUMS from 2 different tables

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have 2 tables first table name is deposit and 2nd table name is withdraw. So I am trying to first total DepositAmount column from deposit table and similarly total amount column from withdraw table after that I subtracted these two columns.

SELECT Sum(deposit.depositamount) - Sum(withdraw.amount) AS qu
FROM   khamdu.deposit
      INNER JOIN khamdu.withdraw
              ON deposit.accountno = withdraw.accountno
WHERE  deposit.accountno = '13'
      AND withdraw.accountno = '13';
Screenshot-(6).png
[Thumbnail for Screenshot-(6).png]
Screenshot-(7).png
[Thumbnail for Screenshot-(7).png]
Screenshot-(8).png
[Thumbnail for Screenshot-(8).png]
 
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, john rawal, and Welcome to the Ranch!

When you post to the forum, always make it clear what the problem is.  In this case, you get the number 12 when you except (I think) 6. Also, avoid screenshots unless there's really no other way to show what's happening. And one last thing: put your SQL between code tags.  Pull down the dropdown control that says "Java" and change it to SQL first.

If I were you, I'd start my simplying the SQL until I see the problem, so maybe:
... and the rest of the SQL code.  Is it what you expect?  Why or why not?  Try simplifying it more:
... and the rest of the SQL.
 
Knute Snortum
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you're going to find that a JOIN will not be useful to you, since you will always get a Cartesian JOIN.  Try something along these lines:

Can you figure out the rest?
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

     
 
Knute Snortum
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did that work?  I was thinking:
 
john rawal
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Did that work?  I was thinking:



Thanks. I figure it out last time when you gave me half answer. One more question I will really appreciate it if you answer. I am printing AccountNo, deposit Date and DepositAmount from deposit table and AccountNo, withDrawDate and Amount from
withdraw table sort by Date Desc. I am using UnionAll and I am getting expecting result. But the problem is I just can't figure out from result that which one is from deposit table and which one is from withdraw because all data is in 3 columns and I knows that's what union do. Can you please tell me any other way so I can get separate columns for each entity. Thanks.

 
Knute Snortum
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I honestly don't think you can do that in SQL.  How are the rows joined?  I think you would need to gather the two selection from deposit and withdraw and merge them programmatically. I'd put all the deposits for one day on the report and then all the withdrawls.
 
john rawal
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:I honestly don't think you can do that in SQL.  How are the rows joined?  I think you would need to gather the two selection from deposit and withdraw and merge them programmatically. I'd put all the deposits for one day on the report and then all the withdrawls.


I figure it out. So the easy way is to just stick with UnionAll and add deposit column in deposit table and write d for every deposit. Similarly create withdraw col in withdraw col and write w for every transaction. That way when I run the query I will be able to identify from which table each result is coming from. I can't create a separate report because I am creating banking application so I need all deposit and withdraw transactions in order they occurred. Thanks For you help.
 
john rawal
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Did that work?  I was thinking:



I just find out that if depositAmount column or withdrawAmount column contains no value then I am getting null. Any idea how to fix that.
 
Knute Snortum
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

john rawal wrote:I figure it out.


Would you like to share the SQL code?
 
Knute Snortum
Sheriff
Posts: 6190
164
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

john rawal wrote:I just find out that if depositAmount column or withdrawAmount column contains no value then I am getting null. Any idea how to fix that.


Try:
 
john rawal
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:

john rawal wrote:I just find out that if depositAmount column or withdrawAmount column contains no value then I am getting null. Any idea how to fix that.


Try:



Thanks I was doing it wrong way. I was putting COALESCE right after SUM(
 
john rawal
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:

john rawal wrote:I figure it out.


Would you like to share the SQL code?


Nothing special just add depositcol in deposit table and withdrawcol in withdraw table. When I am adding any deposit I also add d in deposit column. Similarly when I am withdrawing from withdraw table I am adding w for withdrawcol. That way when I am doing UnionAll I know from which table result is coming from. I know not a great solution but working for me.

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Catch Ernie! Catch the egg! And catch this tiny ad too:
professionally read, modify and write PDF files from Java
https://products.aspose.com/pdf/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!