I have table of bird observations. This is an example:
Unique_ID List_ID Locality Common_name
A1 001 Park Downy Woodpecker
A2 001 Park Hairy Woodpecker
A3 001 Park Carolina Chickadee
B1 002 Campground Blue Jay
B2 002 Campground Hairy Woodpecker
C1 003 Backyard Downy Woodpecker
C2 003 Backyard American Goldfinch
D1 004 School American Goldfinch
D2 004 School Hairy Woodpecker
E1 005 Park Downy Woodpecker
E2 005 Park Carolina Chickadee
I am trying to write a query for PostgreSQL that will only return the Localities that have occurrences of both woodpeckers, Downy and Hairy, together. In the little example table, that would just be the Park. The other localities only have one or the other of the species.
I tried
SELECT List_ID, LOCALITY, COMMON_NAME FROM table
WHERE COMMON_NAME = 'Downy Woodpecker' and COMMON_NAME = 'Hairy Woodpecker';
But returned no results. My table has 1,000s of observations, it's based on eBird data and those two species are generally common throughout the country, so there has to be at least one list_ID where they occur at the same time. In my example table, only Park (based on list_ID 001) meets the condition for what I'm looking for.
If I understand IN, it will return a row that meets either. Any of the example list_IDs would work for that query, but that's not what I want. How do I write a query that forces the WHERE to meet multiple conditions?
First filter the table, group by Locality and return only the localities containing both kinds:
select Locality
from tablename
where Common_name in ('Downy Woodpecker', 'Hairy Woodpecker')
group by Locality
having count(distinct Common_name) = 2
The condition count(distinct Common_name) = 2 in the HAVING clause makes sure that both kinds exist for the same Locality.
If there is no case of duplicate Common_names in the same Locality you may also omit distinct from count().
See the demo.
Results:
| locality |
| -------- |
| Park |