Problem using SUM function with SQLite3 on iPhone

Question

Hi,

I am using SQLite3 in my iPhone app to select data from a table (tbresults), do some calculations on the data and display it in my uitableview. The sql command uses the SUM function and my app doesn't seem to like it.

The method where I do the select from the table is below but processing seems to fail at the following line.

if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK)

The full code for the method is below.

+ (void) getLeagueTable {

Tag_TrackerAppDelegate *appDelegate = (Tag_TrackerAppDelegate *)[[UIApplication sharedApplication] delegate];
appDelegate.tbresultsArray = [[NSMutableArray alloc] init];


const char *sql = "select a.resultid, a.teamname, sum(b.played), sum(b.win), " 
"sum(b.draw), sum(b.lose), sum(b.for), sum(b.against), sum(b.win * 3 + b.draw) "
"from tbteam a, tbresults b, tbseason c where a.teamid = b.teamid and c.active = 'Y'" 
"and b.seasonid = c.seasonid group by b.teamid order by points desc;";


sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

 sqlite3_stmt *selectstmt;

 while(sqlite3_step(selectstmt) == SQLITE_ROW) {

  NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
  tbresults *resultsObj = [[tbresults alloc] initWithPrimaryKey:primaryKey];

  resultsObj.teamname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];

  resultsObj.played = sqlite3_column_int(selectstmt, 2);
  resultsObj.won = sqlite3_column_int(selectstmt, 3);
  resultsObj.drawn = sqlite3_column_int(selectstmt, 4);
  resultsObj.lost = sqlite3_column_int(selectstmt, 5);
  resultsObj.For = sqlite3_column_int(selectstmt, 6);
  resultsObj.Against = sqlite3_column_int(selectstmt, 7);
  resultsObj.points = sqlite3_column_int(selectstmt, 8);

  [appDelegate.tbresultsArray addObject:resultsObj];
  [resultsObj release];
 }
}
else
 sqlite3_close(database);

}

Does anyone know if there is a problem using the SUM function on the iPhone? I have checked and double checked the rest of the code. The database is being opened successfully.

Any help would be appreciated.

Answer 1

What kind of error do you get?

I think your select statement is invalid, stop selecting column a.resultid or include this column in the group by and it will work.

Answer 2

You'd need a proper group by clause at least:

  "select a.resultid, a.teamname, sum(b.played), sum(b.win), " 
"sum(b.draw), sum(b.lose), sum(b.for), sum(b.against), sum(b.win * 3 + b.draw) "
"from tbteam a, tbresults b, tbseason c where a.teamid = b.teamid and c.active = 'Y'" 
"and b.seasonid = c.seasonid group by a.resultid, a.teamname order by points desc;";