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 >
