Mail <sv...@gmx.net> wrote: > My current task is to get the number of foods that belong to each group and > have at least one weight data related to them. > > The query I am using for this is: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND > (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = > t1.ZFOOD) ) > ?) > > When inserting 12 for ZFOODGROUP and 0 for count (I never check for another > count value here, it's always 0), I get a result of > 761 which takes 0.0591 seconds. As I have to fetch the counts of all > available groups, this query is sent in a loop which easily > takes several seconds to finish.
select ZFOODGROUP, count(Z_PK) from ZFFFOOD where Z_PK in (select ZFOOD from ZFFWEIGHT) group by ZFOODGROUP; This gives you counts for all groups at once. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
