"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> If you really want the intersection, perhaps:
Or maybe closer:
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 counts,
count(case when filetype = 'n' then 1 else null end)
as countn
from unique_words
group by word
)
select word, least(countf, counts, countn) from x
where countf > 0 and counts > 0 and countn > 0
order by word;
Cranked out rather quickly and untested.
-Kevin