Query with 2 linked tables and 1 aggregate function (SUM) can't be used without adapter ?? (to be used in a listView)
I need to do a query with 2 linked tables and 1 aggregate function. In my case, the query is working fine but it seems that it can't be used with an adapter (because of the aggregate function).
This is my function to get the query :
public Cursor recupererRecapParFichier(String argument) {
return db.rawQuery("SELECT transactions._id as idT, utilisateurs._id as idU, SUM(transactions.montantTransaction) as total , utilisateurs.nomUtilisateur as nomU FROM transactions " +
"JOIN utilisateurs ON utilisateurs._id=transactions.idUtilisateurTransaction " +
"WHERE transactions.idUtilisateurTransaction=" + argument + " GROUP BY utilisateurs.nomUtilisateur, transactions._id, utilisateurs._id"
, null); }
And now, how I get my cursor :
argument = Long.toString(listeFichiers.getSelectedItemId());
Cursor c 开发者_运维百科= db.recupererRecapParFichier(argument);
startManagingCursor(c);
Now, what can I do to put the data in my listView ? I've tried to use an adapter but there is a problem because of the aggregate function.
Thanks
Your problem is not due to the aggregate function, AFAICT. Your problem is that you have no column in the result set named _ID
. You must have one in order to work with CursorAdapter
.
Ok, the problem was exactly what you've said ! Curso must have one column named "_id" even if it's just an alias...
My solution :
return db.rawQuery("SELECT SUM(transactions.montantTransaction) as total , utilisateurs.nomUtilisateur as _id FROM transactions " +
"JOIN utilisateurs ON utilisateurs._id=transactions.idUtilisateurTransaction " +
"WHERE transactions.idFichierTransaction=" + argument + " GROUP BY utilisateurs.nomUtilisateur"
, null);
精彩评论