Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to retrive values of aggregate sql functions from resultset

 
pradeep vijayakumar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi i ma using aggregate sum functions in the query.I need to retrive the value from it.
Select product,sum(aduration)/60,sum(bduration)/60,sum(TotSales),Sum(netSales),Sum(profit)from temp;

Thanks in advance
 
Satish SN
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,

i feel u could get the values of the aggregate values from sql query in two ways

> by refering the fields according to their datatype from resultset using numbers starting from 1 not zero.

rs.getString(1),rs.getInt(2);

> By alias names give the name after the aggregate function like
sum(total) GrandTotal,

and accessing them using the alias name

rs.getInt("GrandTotal");

I hope this should solve ur problem

Bye
 
pradeep vijayakumar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank sathish
but when i implement it it says invalid cursor state for the following code

select sum(bduration) bdur from tbprofitper;
int a=rs.getInt("bdur");

thanks
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try
select sum(bduration) as bdur from tbprofitper;
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pradeep,
Welcome to JavaRanch!

There are actually two things going on here:
1) you need to call rs.next(); before calling rs.getInt(). Until you call rs.next(), the cursor is pointing to a place before the first row - hence the error message you are seeing.
2) The column names need to match if using the getInt(String) signature. Alternatives are to use the "as" syntax that Purushothaman described or to use getInt(1).
 
Wei Dai
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
> Select product,sum(aduration)/60,sum(bduration)/60,sum(TotSales),Sum netSales),Sum(profit)from temp
You should use:
Select product,sum(aduration)/60,sum(bduration)/60,sum(TotSales),Sum(netSales),Sum(profit)from temp group by product;

while(rs.next(){
//...
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic