-1

I have F_table and S_table in my database.I want to insert the sum of all of the values of the Quantity_column of my F_table into Total_column of the S_table. For doing that what I have to write in JAVA?

Suppose, I have two table one is Product_table and another is Stock_table.In the Product_table there are Columns like ID,Name,Quantity. And in the Stock_table there columns like ID,TotalProduct. Now I want the sum of all of the raw of Quantity Column of Product_table and insert it into the TotalProduct Column of Stock_table. When each time I add any product and its quantity then the TotalProduct will increase automatically. For getting that what I have to do in JAVA?

Sorry if it is a newbie type question. I am actually a beginner. It will be great if anyone help me.

Tabassum
  • 25
  • 2
  • 2
  • 8
  • So you want to take the sum of `Quantity_column` of all rows in `F_table` and insert a new record to `S_table` with a column `Total_column`? Are there any other columns in `S_table`? – fqhv Mar 29 '17 at 18:13
  • You do not write any query in Java. You write a query in SQL and then execute it using JDBC. Or you use an object-relational mapper. But if you are a newbie I think you'd better learn first about JDBC. – Serg M Ten Mar 29 '17 at 18:15
  • @fqhv I've edited the question. will you check that kindly? – Tabassum Mar 29 '17 at 18:23
  • @SergioMontoro I am learning and it will take time you know. But I have to submit my assignment very soon and for that I need to know that logic very badly. :-/ – Tabassum Mar 29 '17 at 18:27
  • So is there only 1 record in Stock_table that is the total quantity of all products? – fqhv Mar 29 '17 at 18:29
  • No. There are four Column ID, TotalProductOfA, TotalProductOfB, TotalProductOfC in the Stock_table. – Tabassum Mar 29 '17 at 18:33
  • So.. The Stock_table has 4 columns? If so can you change the schema? How do you know which quantities go to what column? – fqhv Mar 29 '17 at 18:40
  • There are three different tables of ProductA,ProductB,ProductC and each of them has ID,Name,Price,Date,Image,Quantity columns. Sum of all of the raw of Quantity column of ProductA will be stored in TotalProductOfA column of Stock_table and the same has to be done for the rest of two columns. – Tabassum Mar 29 '17 at 18:49

1 Answers1

1

If I'm understanding correctly you want to get the total quantity for each ID from the product table, and insert those into the stock table, yes?

The below will get you the sum of the quantity, grouped by the id:

select id, sum(quantity) from product_table group by id;

So if you have :

1, test1, 5
1, test2, 10
2, test3, 20
2, test4, 1
3, test5, 5

you would get something like:

1, 15
2, 21
3, 5

Then just insert these into the correct table.

To combine the two you should be able to do something like:

insert into stock_table 
select id, sum(quantity) from product_table group by id;

Keep in mind this is entirely an SQL problem that you're asking about. But to make it jdbc/java related, the jdbc for something like this would be:

String sql = "insert into stock_table select id, sum(quantity) from product_table group by id";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.executeUpdate();

stmt.close();

You would probably want to check the number of rows updated/inserted by stmt.executeUpdate().

Eric B
  • 217
  • 1
  • 14