title: Oracle SQL Count based on conditions
body: <p>I'm trying to create a query to get a count of the number of cables in each asset location. The flow is Project->Asset->Cable. One project can have multiple assets with multiple cables associated to these assets. Below is a snapshot of the data.   </p>
  
  <pre><code>RECORD_ID    PARENT_RECORD_ID    FORM_NAME    PARENT_FORM    FIELD_NAME   VALUE
     1604            1603            asset         project       serial     b345s
     1604            1603            asset         project       location   OH
     1605            1604            cable         asset         zone       green
     1606            1603            asset         project       location   NY
     1607            1606            cable         asset         port       2
  </code></pre>
  
  <p>As you can see above, at the cable level, the PARENT_RECORD_ID is the "RECORD_ID" of the asset. This is just a snapshot as there are thousands of rows, with a bunch of data in between but I tried to only show relevant data.</p>
  
  <p>I need to figure out a way to roll up the number of cables per asset, and then group the assets by 'location'. The 'location' is a value in the FIELD_NAME column for the asset, with the value associated with it in the VALUE column. For example, above, the asset ID 1604, the 'location' is 'OH'.</p>
  
  <p>So far I've been able to get a count of the total number of cables. I just need to figure out how to break these down by the 'location' of their 'asset'.</p>
  
  <pre><code>SELECT
    COUNT(DISTINCT RECORD_ID) "Number of Cables"
  FROM 
    PROJECT_TABLE
  WHERE 
    LOWER(FORM_NAME) = 'cable'
  </code></pre>
  
  <p>Current result of above query:</p>
  
  <pre><code>Number of Cables
       3137
  </code></pre>
  
  <p>What I would like the result to look like:</p>
  
  <pre><code>Location     Number of Cables
    OH                552
    NY               1042
    MI                49
    CA               1494
  </code></pre>
  
  <p>Any help is greatly appreciated! Thanks!</p>