title: Oracle: Aggregated Counts on Tables
body: <p>I'm encountering difficulties trying to get an overall count, as well as counts on each status into a flat table.</p>
  
  <p>To help understand the table structures, here is an example of the 2 tables I am working with.</p>
  
  <p>Heroes:</p>
  
  <blockquote>
  <pre><code>Pin             Name                 Status
  1               Batman               Fighting Crime
  1               Superman             Fighting Crime
  1               Daredevil            Eating Food
  2               Spiderman            Watching TV         
  3               Wolverine            Eating Food
  4               Cyclops              Watching TV
  4               Jean Gray            Watching TV
  4               Prof X               Eating Food
  </code></pre>
  </blockquote>
  
  <p>And then the other table, Locations:</p>
  
  <blockquote>
  <pre><code>Loc_Pin         Place     
  1               Gotham City
  2               Cloud City
  3               New York City
  4               Washington DC
  5               My House
  </code></pre>
  </blockquote>
  
  <p>Using these 2 tables, I'm hoping to get an expected output that would look like:</p>
  
  <blockquote>
  <pre><code>LOCATION          Number of Heroes       Number Fighting         Number Eating          Number Watching TV
  Gotham City       3                      2                       1                      0
  Cloud City        1                      0                       0                      1
  New York City     1                      0                       1                      0
  Washington DC     3                      0                       1                      2
  My House          0                      0                       0                      0
  </code></pre>
  </blockquote>
  
  <p>I feel like I'm almost there. I first have my select statement lined up to get an aggregate count of status and heroes to their respective locations. What my major issue is I need to represent 0 in the tables instead of nulls. So on my update statement, I'm trying to point to itself, which I believe is my problem I am running into.</p>
  
  <p>Here is an example of my SQL query that I am so far. I am only trying to populate "fighting crime" and do a little proof of concept right now and figure I can roll into consideration the other statuses by expanding the case statements:</p>
  
  <pre><code>MERGE
  INTO Counts finalCount
  USING (
      SELECT DISTINCT
          myCounts.PIN
          ,myCounts.status
          ,holder.status
          ,holder.status_count
      FROM
          Counts myCounts
          , (select   count(heroes.status) status_count,
                              heroes.status status,
                              loc.loc_pin loc_pin
  
                      from     heroes_permit_mv heroes,
                              Location loc
  
                      where    heroes.pin = loc.pin
                      group by heroes.status, loc.loc_pin) holder
      WHERE   myCounts.pin = holder.pin
  ) updateMe
  ON (finalCount.PIN = updateMe.PIN)
  WHEN MATCHED THEN
  UPDATE
      SET finalCount.status = CASE WHEN TRIM(updateMe.status) = 'Fighting Crime' THEN updateMe.status_count ELSE 0 END
  </code></pre>
  
  <p>But I'm running into [Error] Execution (2: 6): ORA-30926: unable to get a stable set of rows in the source tables</p>
  
  <p>I've tried to look up this error and the most prevalent answer was to add a DISTINCT statement into my SQL query, which I did and still doesn't seem to help. When I pick my query apart piece by piece outside of the update statement, it all resolves and behaves as expected. It's just when I do my final update statement, I get this error from Oracle.</p>
  
  <p>I've also tried to attack this using an UPDATE statement, but I get another error saying [Error] Execution (21: 8): ORA-01779: cannot modify a column which maps to a non key-preserved table.</p>
  
  <p>Any tips at this point would be greatly appreciated as I've been racking my head against this for the past couple of days.</p>