Capturing result of SUM query

[Garrett Rowe]

I'm stuck trying to capture the output of the SUM
SQL command. I have a table called Coins with columns
Type, and Value. The exercise asks us to (among other things) use the 'SUM'
SQL command to get the sum of the Values column. Here
is the section of code that is giving me trouble.

//Use SimpleDataSource to get connection
Connection conn = SimpleDataSource.getConnection();
 
//...populate database
 
Statement statement = conn.createStatement();
String query = "SELECT SUM(Values) FROM Coins";
ResultSet result = statement.executeQuery(query);
 
while (result.next())//keeps returning false
 

The SimpleDataSource class just provides a convienent method for establishing a connection to the database. I
know there is data in the database because the rest of
my queries return predictable results. If needed, I
could attach my source code. I would be greatful for
any tips you can provide.

Thanks,
Garrett

[Jeanne Boyarsky]

Garrett,
That code looks fine to me. What does the query return if you just run:
"SELECT Values FROM Coins" ?

Obviously, this won't solve your problem, but it may narrow it down. Also, what database are you using?

[Garrett Rowe]

I figured it out.

The problem wasn't that

while (result.next())

was returning false, it was that I was trying to provide a column name when I was examining my ResultSet:

            String query = "SELECT SUM(Value) FROM Coins";
            ResultSet valueResult = statement.executeQuery(query);
            while (valueResult.next())
            {
               double value = valueResult.getDouble("Value");//The error was here!!
               System.out.println(value);
            }

This is a snippet from the working code

            String query = "SELECT SUM(Value) FROM Coins";
            ResultSet valueResult = statement.executeQuery(query);
            int index = 1;
            while (valueResult.next())
            {
               double value = valueResult.getDouble(index);
               System.out.println(value);
            }

Thanks for the help. While going back and trying the query you suggested, the light switch just flipped on.

btw I'm using HSQLDB
[ February 18, 2006: Message edited by: Garrett Rowe ]

[stu derby]

            String query = "SELECT SUM(Value) FROM Coins";
            ResultSet valueResult = statement.executeQuery(query);
            int index = 1;
            while (valueResult.next())
            {
               double value = valueResult.getDouble(index);
               System.out.println(value);
            }

If you'd prefer to get column by name, which is always easier than keeping your indexes straight as the code evolves, most databases/drivers will support this:

            String query = "SELECT SUM(Value) SUM_VALUE FROM Coins";
            ResultSet valueResult = statement.executeQuery(query);
            while (valueResult.next())
            {
               double value = valueResult.getDouble("SUM_VALUE");
               System.out.println(value);
            }

[Garrett Rowe]

Thanks for the tip stu. That's one that wasn't in my text. That's exactly why I love this forum.

[Jeanne Boyarsky]

Many databases require an "as" when definining a column name alias:
"String query = "SELECT SUM(Value) as SUM_VALUE FROM Coins";"