• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to code in sql insert and subtract the 2 value

 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sir good evening i need help, i have a 2 tables product info and daily sales in product info i have a prod_id,prod_name,stocks, and in daily sales i have prod_id,prod_name,qty ,price,total then the daily sales is inputting by user then i need the deduction between stocks and qty after that multiplied the price and qty can you help sir ....
 
Knute Snortum
Bartender
Pie
Posts: 2878
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using Java and JDBC?  I would deduct the quantity from the stock in Java and then do an Update in SQL instead of an Insert.
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not clear from the original post that it's necessary to update any SQL tables at all. We have this:

tony mirador wrote:i need the deduction between stocks and qty after that multiplied the price and qty


but that could equally well mean that the result of those calculations should be displayed somewhere. So tony, what exactly are you having problems with? Extracting data from an SQL database? Joining two tables? Doing calculations in SQL? In Java? A more in-depth problem description would be very helpful.
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i use java sir ... the table product_info we have a record then the daily_sales is inputting by the user the stocks from product info will be deduct by the quantity of daily_sales  for example i have a stocks 10 pcs of candy then the user will input the details of the candy and automatic deduct in the stocks base in the quantity of sales ,then times the quantity to the prices and the details we input in daily_sales we be save in table.
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay... so that's what you want to do. And what is your problem with that?
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i need sir is how to code that in java please help me for my learning in java ....just give me sir an example code, i check everything in google for the example ...
 
Knute Snortum
Bartender
Pie
Posts: 2878
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is not simple enough to just give an example.  Here is a tutorial for JDBC, but it assumes you have some Java knowledge.  If you need to Google it, try java jdbc example.
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't you show us what you have so far, and we can start from there?
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
good evening sir ..i tell you frankly my skill in java is very poor i will learned only in youtube i practice to code base on i see how to code i create some project to practice my self in java netbeans and my database is sqlite i learned how to insert,delete,update just only a basic code then i try to create some project to combine everything i learned but one of the button i create i stock in the code because i dont know how to do the code please sir help me....here my code and the other code need  for these button i don't have  idea how to put in ..

// Purchace Button:
        try{
       
        String sql1="insert into Sales_Info(Date,Product_Id,Product_Name,Discription,Model_Number,(Quantity*Selling_Price))value (?,?,?,?,?,?,?,?)";
       
         pst=conn.prepareStatement(sql1);
         pst.setString(1, sales_date_txt.getText());
         pst.setString(2, sales_ID_txt.getText());
         pst.setString(3,sales_Product_name_txt.getText());
         pst.setString(4, sales_Discription_txt.getText());
         pst.setString(5, sales_Prices_txt.getText());
         pst.setString(6, sales_Quantity_txt.getText());
         pst.setString(6, sales_Saleprice_txt.getText());
      
         pst.execute();
       
          JOptionPane.showMessageDialog(null, "BUYED");
       
         pst=conn.prepareStatement(sql1);
       
       
       
       
        }catch(Exception e){
   
        JOptionPane.showMessageDialog(null, e);
    }
    }                                               

    private void sales_ID_txtActionPerformed(java.awt.event.ActionEvent evt) {                                            
        // TODO add your handling code here:
    }                                           
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sir good evening ....here my new code i try in to execute direct in sqlite its working but when i put in the button in netbeans is not working  can you check what is my error ...

// Purchace Button:
        try{
       
        String sql="Insert into Sales_Info(Date,Product_Id,Product_Name,Discription,Model_Number,Quantity_Sales,Selling_Price,Sales_Prices) values (?,?,?,?,?,?,?,?);Select sum(Quantity_Sales*Sales_Prices)as Total_Sales from Sales_Info";
               
              
       
         pst=conn.prepareStatement(sql);
         rs=pst.executeQuery();
       
         pst.setString(1, sales_date_txt.getText());
         pst.setString(2, sales_ID_txt.getText());
         pst.setString(3,sales_Product_name_txt.getText());
         pst.setString(4, sales_Discription_txt.getText());
         pst.setString(5, sales_Prices_txt.getText());
         pst.setString(6, sales_Quantity_txt.getText());
         pst.setString(7, sales_Saleprice_txt.getText());
        
         //String sql2="Select sum(Quantity_Sales * Selling_Price)as Total_Price from Sales_Info";
        // pst.execute(); 
        
          if(rs.next()){
         
            String sum=rs.getString("sum(Quantity_Sales*Sales_Prices)");
            sale_total_txt.setText(sum);
      
        pst.execute();
       
          JOptionPane.showMessageDialog(null, "BUYED");
       
        
         }
        
       
       
       
       
        }catch(Exception e){
   
        JOptionPane.showMessageDialog(null, e);
    }
    }                     
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tony mirador wrote:is not working  can you check what is my error ...


Not really. For one thing we have no idea what "not working" means and for another thing you have told us nothing about "my error". Have a look at our FAQ page TellTheDetails to find out how to ask a good question on a forum.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your sql has two statements in it, which won't work.

You need to do the INSERT first, then the SELECT.

Can't help much more than that as I don't really know what it is you are trying to achieve.
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes Sir dave I'm using two statement i need insert first then select to compute the sales_quantity and sale_prices value can you help me sir dave ?.....Sir paul I mean is "not working" the output of my code not the message in sql ....
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tony mirador wrote:Sir paul I mean is "not working" the output of my code not the message in sql ....


It would help all of us a lot if you would spend more then 30 seconds in posting your reply. I already posted a link for you to follow (that "TellTheDetails" link in my earlier reply). Please follow the link and read it and try to understand it. Then post a reply which explains "not working" in terms of what you read on that page.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tony mirador wrote:Yes Sir dave I'm using two statement i need insert first then select to compute the sales_quantity and sale_prices value can you help me sir dave ?


Then you need to do two calls.

First INSERT then SELECT.
 
tony mirador
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sir dave good evening...sir how can i do that to insert first then select can you give me idea how can i do that just give me example sir dave...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic