Rohan21
Rohan21

Reputation: 353

Sum of all values of one column from sql

I need to get the sum of all values of one column and put it in a textField. i tried the code below but i get an error. - The column name Sum(Price) is not valid.

     String sql="Select Sum(Price) from sold";
                              pst=con.prepareStatement(sql);
                              rs=pst.executeQuery();
                              if(rs.next()){
                                  String sum = rs.getString("Sum(Price)");
                                  textField_2.setText(sum);

com.microsoft.sqlserver.jdbc.SQLServerException: The column name Sum(Price) is not valid.

Upvotes: 2

Views: 11198

Answers (4)

realProgrammer
realProgrammer

Reputation: 41

Try this.

        int sum = 0;
        Statement st = conn.createStatement();
        ResultSet res = st.executeQuery("SELECT SUM(price) FROM sold");
        while (res.next()) {
        int c = res.getInt(1);
        sum = sum + c;

        String str = Integer.toString(sum);
        textField_2.setText(str);

this will work

Upvotes: 0

juergen d
juergen d

Reputation: 204934

Use an alias for the calculated column

Select Sum(Price) as sum_price from sold
...
String sum = rs.getString("sum_price");
textField_2.setText(sum);

Upvotes: 1

Alex K.
Alex K.

Reputation: 176016

Use the overload that takes the ordinal column number as the result of an aggregate is nameless by default: rs.getString(1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use an alias:

 String sql="Select Sum(Price) as sumprice from sold";
                          pst=con.prepareStatement(sql);
                          rs=pst.executeQuery();
                          if(rs.next()){
                              String sum = rs.getString("sumprice");
                              textField_2.setText(sum);

I don't know if the values get converted correctly. The value is a number of some sort. Perhaps you want:

 String sql="Select cast(Sum(Price) as varchar(255)) as sumprice from sold";

Upvotes: 4

Related Questions