Richard Huxton wrote:
> Madison Kelly wrote:
>> Hi all,
>>
>> I've got a query that looks through a table I use for my little
>> search engine. It's something of a reverse-index but not quite, where
>> a proper reverse index would have 'word | doc1, doc3, doc4, doc7'
>> showing all the docs the keyword is in, mine has an entry for eac
>>
>> I've got a query like:
>>
>> SELECT
>> sch_id, sch_for_table, sch_ref_id, sch_instances
>> FROM
>> search_index
>> WHERE
>> (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
>> AND
>> sch_for_table!='client'
>> AND
>> ... (more restrictions)
>> ORDER BY
>> sch_instances DESC;
>>
>> This returns references to a data column (sch_ref_id) in a given
>> table (sch_for_table) for each matched keyword.
>>
>> The problem I am having is that two keywords might reference the
>> same table/column which would, in turn, give me two+ search results
>> pointing to the same entry.
>>
>> What I would like to do is, when two or more results match the same
>> 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically,
>> the 'sch_instances' column is the number of times the given keyword is
>> found in the table/column. I'd like to add up the number in the
>> duplicate results (to give it a higher accuracy and move it up the
>> search results).
>
> You'll want something like:
>
> SELECT
> sch_id, sch_for_table, sch_ref_id,
> SUM(sch_instances) AS tot_instances
> ...
> GROUP BY
> sch_id, sch_for_table, sch_ref_id
> ORDER BY
> tot_instances DESC;
>
> The key word to search the manuals on is "aggregates" (sum(), count() etc).
>
This is *exactly* the pointer I needed, thank you!
Sad thing is that I even used "GROUP BY" before... had just forgotten
about it. ^_^;
Madison
