Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl> wrote:
> I would like the answer to be "the number of times the word
> appears in all three the queries", the intersection of the three
> queries.
That's still not entirely clear to me. If there are two 'f' rows,
three 's' rows, and four 'n' rows, do you want to see an answer of 2
(which seems like the intersection you request here), 9 (which is
the sum), 24 (which is the product), or something else?
If you really want the intersection, perhaps:
with x as
(
select
word,
count(*) as countall,
count(case when filetype = 'f' then 1 else null end)
as countf,
count(case when filetype = 's' then 1 else null end) as
as counts,
count(case when filetype = 'n' then 1 else null end) as
as countn
from unique_words
)
select word, least(countf, counts, countn) from x
where countf > 0 and counts > 0 and countn > 0
order by word;
-Kevin