title: MySQL: getting a row count based on a correlated subquery of a subquery
body: <p>I apologize if this has been answered else where, I've been pulling my hair out searching but haven't found anything yet.</p>
  
  <p>I have the following tables:</p>
  
  <pre><code>    Table: STATES
  +------------+---------+
  | name       | stateID |
  +------------+---------+
  | Florida    |  FL     |
  | California |  CA     |
  | New Jersey |  NJ     |
  +------------+---------+
  
  Table: AREAS
  +-------+---------+-------+
  | name  | stateID | locID |
  +-------+---------+-------+
  | Area1 | FL      | 1     |
  | Area2 | FL      | 1     |
  | Area3 | FL      | 2     |
  | Area4 | NJ      | 3     |
  | Area5 | NJ      | 3     |
  | Area6 | NJ      | 4     |
  | Area7 | CA      | 5     |
  | Area8 | CA      | 6     |
  | Area9 | CA      | 7     |
  +-------+---------+-------+
  
  Table: LOCATIONS
  +----------+-------+
  | name     | locID |
  +----------+-------+
  | Orlando  | 1     |
  | Brevard  | 2     |
  | North NJ | 3     |
  | South NJ | 4     |
  | B Hills  | 5     |
  | East LA  | 6     |
  | SanFran  | 7     |
  +----------+-------+
  </code></pre>
  
  <p>The result I'm trying to get is:</p>
  
  <pre><code>| STATES.name | LOCATIONS.name | COUNT(locations in state) | AREAS.name
  </code></pre>
  
  <p>I'm able to get everything but the count easily enough, but I just can't get how many locations are in each state. It seems like this should be a simple thing, am I just missing something?</p>
  
  <p>Here's the query I've been working with:</p>
  
  <pre><code>SELECT 
      STATES.name AS state
      ,LOCATIONS.name AS location
      ,AREAS.name AS area
      ,(SELECT
          COUNT(*) 
       FROM 
          (SELECT
              areaID
          FROM
              AREAS
          WHERE 
              AREAS.stateID = STATES.stateID 
           GROUP BY 
              AREAS.locID
          ) AS area_count
  
      ) AS row_count
  FROM
      STATES
      LEFT JOIN AREAS ON STATES.stateID = AREAS.stateID
      LEFT JOIN LOCATIONS ON AREAS.locID = LOCATIONS.locID;
  </code></pre>
  
  <p>Of course this fails due to there not being any STATES.stateID in subquery 1 for subquery 2 to reference. I've tried passing the stateID along by <code>JOIN</code>ing the table in subquery 1, as well as <code>JOIN</code>ing it in subquery 2 instead of trying to reference it in the <code>WHERE</code> statement. This also fails given that the whole of subquery 2 is part of the <code>WHERE</code> statement for subquery 1...</p>
  
  <p>I've also tried isolating the queries to pull just <code>AREAS.stateID</code> and <code>COUNT(locations in state)</code> but to no avail, I keep getting the number of areas in the state, not the number of locations:</p>
  
  <pre><code>select AREAS.stateID
  , COUNT(AREAS.locID)
  FROM AREAS group by AREAS.stateID
  </code></pre>
  
  <p>Tossing a <code>DISTINCT</code> into the <code>COUNT()</code> gets me closer but some of the values are still off, not sure where it's getting the numbers from as they don't match the number of locations for all cases, but most of them...</p>