We have a database that collects data related to corals, specifically how coral grows on rubble.
We have a table that collects data on each rubble piece. From this table, there is a one-to-many relationship with a coral table, each piece of coral has a unique record. Thus, one piece of rubble may have 0 or more corals at one time.
We then record a year later whether the unique coral has survived.
We want to write a query that counts the number of corals on each rubble piece and how many survived one year later.
Thanks!