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
- I can’t get values of
rs.getString(1)
andrs.getInt(3)
andrs.getInt(4)
value but got the value ofrs.getString(2)
.How to get values ofrs.getString(1)
andrs.getInt(3)
andrs.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.