• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Devaka Cooray
  • Ron McLeod
  • paul wheaton
Saloon Keepers:
  • Tim Moores
  • Piet Souris
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Frits Walraven
  • Scott Selikoff

How to insert a SUM with a SQL Sentence from Java in the same row

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Everybody I'm New,

I use this forum because i am creating a program where I can register a person and how much money debt, how much pay and its total.
The idea is store it in a database.

I have can not insert the subtraction of SUM(Deuda-Abono) in the colum total in the same row, the most I could do was generate the total but duplicating the line with this may be i am wrong





Here is how look my program
[url=https://prnt.sc/78g8QA3IWgsi]


This is my code when i run the boton Add, as you can see I dont insert the colum total so I think that I could do the operation and storage it in the same row with another sentence.





Sentence to Substract:

INSERT INTO Clientes (Total)
SELECT SUM(Deuda-Abono)
From Clientes
Group By ID;




I am new Programming so if you Could help me or if you can give another idea in how i can do my program I would appreciate it.

Thanks.
Screenshot_2.png
[Thumbnail for Screenshot_2.png]
 
Marshal
Posts: 76829
366
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch

Please indent your code and use the code button, which will make the code easier to read. You can hide all sorts of errors behind badly‑formatted code. I think you have added mistakes when copying your code; you wrote oClie ntes when I think you meant oClientes.
Please start by disconnecting your code from the GUI. Print out the results to System.out. It is much more difficult to find errors via a GUI.
I shall add you to our Databases forum.
 
Juan Jose
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi campbell, Thank you for your answer, excuseme for the post.

I have been doing the program in java maven with javax.swing.

I have to do it in the Terminal ?
 
Campbell Ritchie
Marshal
Posts: 76829
366
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you shouldn't add a GUI until you have the program working correctly at the command line/terminal. The more intermediate stages you have, the more opportunity there is for something to go wrong, and the more difficult it is to find the error. What happens when you use that query directly on the database?
 
Juan Jose
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for your answer, I will have to rediseign my program to do it without GUI.

When I use the Query :



In the images that I attached, you can see that in the screenshot 4 The query do  the operation ok but in a new line I would need insert it at the end of the same row, but the second problem is when i run for the second time it duplicate all the registers as you can see in the screenshot 6.

I have this methods in the frm:

This is for the table



This is to show the data from the database in the table




This is to catch the data from the boxes in the GUI




This is to clean the boxes




AND THIS do the query to insert data in the database




This is the mehot that I tried to insert the SUM It works but not As i Need you can see it in the screenshot 7





Screenshot_6.png
Output after second run
Output after second run
Screenshot_4.png
First run
First run
Screenshot_7.png
Run from the GUI
Run from the GUI
 
Juan Jose
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is my table in the database,(screenshot 8) may be if I can use an excep or a validator to avoid the duplicate data, may be the query that a i use is wrong or what i am trying to do it's wrong from the root jeje

Thank you for your time.

Regards.
Screenshot_8.png
Table database
Table database
 
Saloon Keeper
Posts: 9717
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What if you did it with two SQL calls instead of one. The first to add the two columns and put the result in the total column. The second step to sum  the total column and insert a new record. Of course if you want to run step two another time you'd have to delete the total record at the end first.
 
Carey Brown
Saloon Keeper
Posts: 9717
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
...which got me to thinking. Why are you inserting a new row for the total at all, that could just be a transient step in the report generation process.
 
Juan Jose
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Finally you i got and idea with your last reply.

I have found a solution doing and UPDATE query:



That I inserted the Query in the method :



It was more easier than I thought...

Thank you for your answers and excuseme for my novice
Screenshot_9.png
[Thumbnail for Screenshot_9.png]
 
Carey Brown
Saloon Keeper
Posts: 9717
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
 
Look ma! I'm selling my stuff!
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic