title: SQL - count of number of times a foreign key appears in a table
body: <p>Schema Info: <img src="https://i.stack.imgur.com/NQtsj.png" alt="SCHEMA"></p>
  
  <p>3 tables are concerned: SIGHTING, SPOTTER, AND ORG</p>
  
  <p>SIGHTING references SPOTTER through FK SpotterId. </p>
  
  <p>SPOTTER references ORG through FK OrgId.</p>
  
  <p>I would like a query to return two columns; one for a list of ORG.OrgName, and another for the respective total count of Spotter_ID appearances in SIGHTINGS for the corresponding ORG.OrgID.</p>
  
  <p>What I have done below returns the incorrect counts for each row returned:</p>
  
  <pre><code>SELECT ORG.ORG_NAME AS ORG_NAME,
         (SELECT COUNT(SIGHTINGS.SPOTTER_ID) 
            FROM SIGHTINGS
               , SPOTTERS 
           WHERE SIGHTINGS.SPOTTER_ID = SPOTTERS.SPOTTER_ID 
            AND SPOTTERS.ORG_ID=ORG.ORG_ID) AS ORG_COUNT 
    FROM ORG;
  </code></pre>