Objective C query - using SQLite with SUM function for iPhone

Josey Wales

Hi,

I am using SQLite3 in my 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)

+ (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?



Any help would be appreciated.

bladez

I dont think should be an issues on the iphone.
The only thing I can think off old ipods did not have floating point unit so could not do calculations on decimal numbers. The arm processor in the Iphone probably has a FP unit.

re the sql you are using have you debugged oustside c. If not try SQLite Manager for firefox handy.

your group by clause is wrong.

"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;";

You probably dont need the order by clause simplest correct satement for testing would be

"select a.resultid, a.teamname, sum(1)
"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;";

Josey Wales

I'm testing my app on the iPhone simulator with the 3.0 OS so I assume it is nothing to do with the fact that older iPods can't do calcuations on decimal numbers.

I have ran the SQL in a terminal session and in the Firefox plugin for SQLite. The SQL works fine and it brings back the data I want, arranged exactly the way I want it. The SQL is written to bring back a league table which is why I order by 'points' and only group by the 'teamid'.

Thanks for the response.
The reason I have the 'group by' clause the way it is