Count number of occurrences for a list of search terms
We have a table PROPERTY with an Oracle Text index on the DESCR column
I can count the actual number of occurrences of a term (e.g. ‘central heating’) by either
SELECT id, score(1)
FROM property
WHERE contains (descr,
'<query>
<textquery grammar="CONTEXT">
"central heating"
</textquery>
<score datatype="INTEGER" algorithm="COUNT" />
</query>',1) > 0;
OR
SELECT id, score(1)
FROM property
WHERE contains(descr, 'DEFINESCORE ("central heating", OCCURRENCE)', 1) > 0;
PROBLEM:
I wish to sum up all the occurrences of a list of terms e.g. ‘central heating’ and ‘parking’