Looked in the documentation and the answer seems to use total instead of sum. This is for the ratio field, not the 2 count fields. Looks all sorted now and thanks again.
RBS On Sat, Feb 14, 2015 at 5:13 PM, Bart Smissaert <bart.smissaert at gmail.com> wrote: > One more thing. How would I get the ratio of the 2 counts, so count1 / > (count1 + count2) ? > > select NAME, > sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1, > sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2, > count1 / (count1 + count2) as ratio > from Items join Descriptions > group by NAME > > is not allowed and > > select NAME, > sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1, > sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2, > sum(INSTR(FULL_TEXT, DEFINITION1) > 0) / > (sum(INSTR(FULL_TEXT, DEFINITION1) > 0) + > sum(INSTR(FULL_TEXT, DEFINITION2) > 0)) as ratio > from Items join Descriptions > group by NAME > > Gives integer values, so no decimals. > > RBS > > > > On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik <igor at tandetnik.org> > wrote: > >> On 2/14/2015 11:32 AM, Bart Smissaert wrote: >> >>> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I >>> INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) >>> INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT, I.DEFINITION2) > 0) >>> GROUP BY I.NAME >>> >> >> You are doing a cross product of DESCRIPTIONS to itself. I expect the two >> counts are the same, and are equal to a product of the actual, true counts. >> >> You are looking for something like this: >> >> select NAME, >> sum(INSTR(FULL_TEXT, DEFINITION1) > 0), >> sum(INSTR(FULL_TEXT, DEFINITION2) > 0), >> from Items join Descriptions >> group by NAME; >> >> -- or >> >> select NAME, >> (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1) >> > 0), >> (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2) >> > 0) >> from Items; >> >> The first one would probably work faster. >> -- >> Igor Tandetnik >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
