oracle sql ranking and count with conditional statements

Question

I am stuck with the process of categorizing rows based on particular conditions. I am hoping to get through this with the support from you guys! Basically, I am trying to create two new columns to the table by applying some particular formulas. Those columns are for ranking volume for each year and also for categorization by specifying whether each prac was included in the top 3 claims in 2014 or not. In the below examples, the columns highlighted in light blue are the ones I want to create.

My query is as below - right now I have a problem with writing codes for the two last lines above from, group by and having do not seem to work within those parentheses. Please help!!

select    
    fiscal_year,
    prac,
    count(*) "CLAIMS",
    sum(paid_amount) "COST",
    row_number() over (group by fiscal_year order by count(*) desc) "Annual Rank by Claims",
    case
        when row_number() over (having fiscal_year = '2014' order by count(*) desc) < 4
        then  'YES'
        else 'NO' 
    end "PRAC TOP 3 IN CLAIMS 2014"    
from mys.mv_claim
where category = 'V'
group by  fiscal_year,
    prac

/

Answer 1

I've never seen GROUP BY and HAVING used in a partitioning spec; I suspect it's a syntax error. I think your query needs to look more like:

WITH t as (
 select    
  fiscal_year,
  prac,
  count(*) "CLAIMS",
  sum(paid_amount) "COST"  
 from mys.mv_claim
 where category = 'V'
 group by  fiscal_year, prac
)
SELECT 
  t.*,
  rank() over (partition by fiscal_year order by claims desc) "Annual Rank by Claims",
  case
    when prac in(select prac from (select * from t where fiscal_year = 2014 order by claims desc ) where rownum <= 3)
    then  'YES'
    else 'NO' 
  end "PRAC TOP 3 IN CLAIMS 2014"
from t

I've used rank instead of row number, as two identical claim counts will produce a rank value the same. Rank will then skip the next rank (two 999 claim counts will each rank 1, a 998 claim count will rank 3. If you want it to rank 2 use dense_rank)

Let me know if you get any errors with this, as the only thing I'm not certain will work out is the subselect that retrieves the top 3 pracs. That can be done with a left join instead (which i have greater confidence in)