sqlite sum() into nsinteger

Question

I want to build a function which takes the sum of all rows in a column called hours. it should then return an integer value, which i am going to use to multiply with another number.

-(NSInteger)calculateTotal{

FMDatabase *dbHandler = [FMDatabase databaseWithPath:[Utility getDatabasePath]];

[dbHander open];

FMResultSet *results = [dbHandler executeQuery:@"SELECT SUM(hours) FROM inputs"];

NSInteger totalHours;
while ([results next]) {
    totalHours = [results intForColumn:@"hours"];
}

return totalHours;
}

but it doesnt work, it return 0, and it comes with a warning saying no column called "hours"

Did you try looking at your database and seeing if you had a column called 'hours'? — Carl Veazey, Aug 30 '12 at 22:38

score 0 · Accepted Answer · answered Aug 31 '12 at 01:09

I think most database engines would name the column in the result of your query after the outermost aggregate function -- SUM.

Try changing your query to SELECT SUM(hours) AS hours FROM inputs. (Or, if you're querying Oracle, you unlucky person, the query is without the AS.)

score 0 · Answer 2 · answered Aug 31 '12 at 05:46

The following code will work:

-(NSInteger)calculateTotal
{
  FMDatabase *dbHandler = [FMDatabase databaseWithPath:[Utility getDatabasePath]];

  if(![dbHander open])
  {
        NSLog(@"Could not open DB, try again");
        return -1; //return some value that tells you that there was an error
  }

  FMResultSet *results = [dbHandler executeQuery:@"SELECT SUM(hours) AS sum_hours FROM inputs"];

  NSInteger totalHours = -1;
  while ([results next]) {
      totalHours = [results intForColumn:@"sum_hours"];
  }

  [results close];
  [dbHandler close];
  return totalHours;
}

sqlite sum() into nsinteger

2 Answers2