For some reason, probably because I'm not understanding very well, I'm
getting some duplicated rows for this query (below).
My understanding is that "group by" brings all the rows together where the
indicated columns are equal and then applies the aggregate to the column
indicated; in this case: "sum".
What am I missing? I'm not seeing why this query doesn't group everything
and then return unique rows.
Please let me know if you need more info and I will send it.
SELECT
index_uri.uri,
index_uri.description,
index_uri.title,
index_type.type,
index_type.icon,
SUM(index.word_count)
FROM
index, index_word, index_uri, index_type
WHERE
(
index_word.word ~'eat.*' -- could be more or fewer
comparisons here.
OR index_word.word ~'disord.*'
)
AND
index_word.word_id=index.word_id
AND
index_uri.uri_id = index.uri_id
AND
index_type.type_id = index_uri.type_id
GROUP BY
index_uri.uri,
index_uri.description,
index_uri.title,
index_type.type,
index.word_count,
index_type.icon
ORDER BY
sum
DESC
Matt Friedman