title: Oracle SQL create a virtual column, do a max and then count and displaying records
body: <p>Sorry if the title is a bit misleading but I don't know how else to put it. Anyways back to problem at hand. I have 2 tables WSM(contains warehouse id, name and risk associated with it) and SUTSM_REP(store and warehouse relation). Below are values what they hold:
  <a href="https://i.stack.imgur.com/JJL0y.png" rel="nofollow noreferrer">WSM table</a></p>
  
  <p><a href="https://i.stack.imgur.com/QGW7M.png" rel="nofollow noreferrer">SUTSM_REP</a></p>
  
  <p>Every warehouse has a risk associated with it(4 risks categories - Low, Medium, High &amp; Critical) and a store gets stuff from multiple warehouses. If a store gets stuff from 3 warehouses(say for eg w1(medium risk), w2(high), w3(low)) then the risk associated with the store is 'High'. Similarly if a store gets stuff from 2 warehouses - 1 with medium and another with low risk then the store gets 'Medium' risk. I hope I am clear with this point. I was asked to bring up the count of Stores with each risk. So what I did was I created another table with risk and risk id. I assigned 1 to Low, 2 to medium, 3 to high and 4 to critical. Then I joined the 3 tables along with max of risk id and then did a count of that. Below is my query :</p>
  
  <pre><code>select RID, count(sp_id)
  from
    (select a.sp_id, max(c.r_id) AS RID
     from SUTSM_REP a,
          WSM b,
          wsm_rid c
     where a.sm_id = b.sm_id and b.smr = c.smr
     group by a.sp_id) t
  group by RID;
  </code></pre>
  
  <p>And the result is :<a href="https://i.stack.imgur.com/5kBoP.png" rel="nofollow noreferrer">my query result</a></p>
  
  <p>But boss doesn't want a 3rd table to be created and he wants Risk names (Low, Medium, High, Critical) instead of Risk IDs. So I modified the query to :</p>
  
  <pre><code>with t as (
    select sm_id, sm_name, smr, DECODE(smr, 'Low', 1,
                                      'Medium', 2,
                                      'High', 3,
                                      'Critical', 4,
                                      0) RISK_ID 
     from wsm )
  select rid, count(sp_id) from 
      ( select sp_id, max(risk_id) rid 
        from (select a.sp_id, b.sm_id, b.risk_id 
              from SUTSM_REP a, t b 
              where a.sm_id=b.sm_id) 
         group by sp_id ) 
  group by rid;
  </code></pre>
  
  <p>I created a temp table using the wsm and added the risk id to the risk category/level. And then used the same logic as earlier. <a href="https://i.stack.imgur.com/lLK9G.png" rel="nofollow noreferrer">Output of this query</a> is :</p>
  
  <p>We use Oracle SQL. Can anyone please help me or point me in the direction of how to get the names of Risk Categories/Levels? I am at my wits end here. Thanks in advance to any posters for their help.</p>