query database/output count and summation to servlet?

[Nicole Jones]

I have this database query that adds the total dollar amount by funder and counts the number of projects per funder. obviously, neither the total or the count is stored in my database....should it be? because how would i access the count and total from a servlet if neither are stored the database?

here is my query:

SELECT FUNDER_NAME, COUNT(FUNDER_NAME) AS NUMBER_PROJECTS, SUM (FY_FUNDING) AS FUNDER_TOTAL FROM MYTABLE GROUP BY FUNDER_NAME ORDER BY FUNDER_TOTAL DESC

Any help will be greatly appreciated.

[Nicole Jones]

Nevermind, doh. I figured it out.

This is the desired query:

SELECT FUNDER_NAME, COUNT(FUNDER_NAME) AS funderCount, SUM (FY_FUNDING) AS funderTotal FROM MYTABLE GROUP BY FUNDER_NAME ORDER BY funderTotal DESC

code to set the alias columns:

f.setFunderCount(rs.getInt("funderCount"));
f.setFunderTotal(rs.getBigDecimal("funderTotal"));  

[Paul Clapham]

Yep, that should work fine. Here's another method which doesn't require aliases for the computed columns:

f.setFunderCount(rs.getInt(2));
f.setFunderTotal(rs.getBigDecimal(3));  

(The columns are numbered from left to right in the query, starting at 1.)