Total credits and debits of usee month-wise

Issue

This Content is from Stack Overflow. Question asked by Alok Maity

I have one database schema Transaction table. Is it possible to write in one sql query to show Month which is not null ,Currency_Type(which is INR), total credits(which is CREDIT), total debits(which is DEEDIT) month wise(which is not null)?

I wrote the following code but it’s not working:


Con = DriverManager.getConnection("jdbc:mysql://localhost:3306/CREDITDEBITDb","Amaity","Alok@2022");
            
          PreparedStatement pst1;
         
          pst1 = Con.prepareStatement("select Month,Currency_Type,TotalCredits,TotalDebits from (select Month,Currency_Type,SUM(case when Type='CREDIT' then Amount else 0 end) AS TotalCredits,SUM(case when Type='DEBIT' then Amount else 0 end) AS TotalDebits from TransactionTbl where  Month IS NOT NULL group by Month,Currency_Type) as TransactionTbl1 ");
          
          
            ResultSet Rss= null;
            Rss=pst1.executeQuery();
           if(Rss.next()){
                long amt=Rss.getInt(4);
System.out.prinyln("Amount"+amt) //null
}

In written Query where I mistaken:

SELECT Month,Currency_Type, 
       totalcredits,
       totaldebits
FROM   (SELECT month,
               Sum(CASE
                     WHEN type = 'CREDIT' THEN amount
                     ELSE 0
                   END) AS TotalCredits,
               Sum(CASE
                     WHEN type = 'DEBIT' THEN amount
                     ELSE 0
                   END) AS TotalDebits
        FROM   transactiontbl where  Month IS NOT NULL 
        GROUP  BY month, Currency_Type) AS TransactionTbl1 

But I faced the following problems

  1. I can’t get values of
    rs.getString(1) and rs.getInt(3)
    and rs.getInt(4) value but got the value of rs.getString(2).How to get values of rs.getString(1) and rs.getInt(3)
    and rs.getInt(4) ?



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?