[sqlite] GROUP BY with self join

[Igor Tandetnik]

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