I can get the State_sls_Total using the following query, but the State_Rank, gives me what is observed above, rather than the desired results. After reading the manual, I now understand why I get the results I do. I am pretty sure I can use a sub-query, but with over 700K records and a join of 7 tables, I was hoping I could use one of these analytic functions to improve my performance.
SUM(sls) OVER (PARTITION BY State) AS State_SLS_Total,
RANK() OVER (ORDER BY State_sls_total DESC) AS State_Rank