Oracle SQL Rank

Question

I have this table as per below:

[Table A][1]

B_NUMBER_COUNTRY    OUTGOING_CARRIER    MINUTES
CAN                 A                  1,045.71 
CAN                 B                      7.98 
CAN                 C                    973.52 
FRA                 A                    566.19 
FRA                 B                    521.52 
FRA                 C                     27.03 
FRA                 D                    549.14 
FRA                 E                      0.21 
USA                 A                     32.57 
USA                 B                    303.17 
USA                 C                  9,837.53 
USA                 D                      3.91 
USA                 E                      0.07 
USA                 F                  2,469.00 
USA                 G                     67.68 
USA                 H                      0.37 
USA                 I                    933.72 

I need to rank b_number_country based on the sum of minutes.

In the above case, the total minutes for USA is 13K, CAN is 2K and FRA is 1.6K. So the ranking should be USA - 1, CAN - 2 and FRA - 3. By adding the rank column, it should be showing as per below:

[Table A (rank)][2]

B_NUMBER_COUNTRY    OUTGOING_CARRIER    MINUTES  RANK
CAN                 A                  1,045.71  2
CAN                 B                      7.98  2
CAN                 C                    973.52  2
FRA                 A                    566.19  3
FRA                 B                    521.52  3
FRA                 C                     27.03  3
FRA                 D                    549.14  3
FRA                 E                      0.21  3
USA                 A                     32.57  1
USA                 B                    303.17  1
USA                 C                  9,837.53  1
USA                 D                      3.91  1
USA                 E                      0.07  1
USA                 F                  2,469.00  1
USA                 G                     67.68  1
USA                 H                      0.37  1
USA                 I                    933.72  1

I am unable to get the right query to do this. Every attempt, it includes both b_number_country and outgoing_carrier as part of the ranking.

Answer 1

Edited based on comment:

You need two steps, calculate the sum of the minutes first and then rank them:

SELECT ...,
   DENSE_RANK()
   OVER (ORDER BY sumMinutes DESC) -- must be DENSE_RANK
FROM
 (
   SELECT b_number_country, interval_of_day, outgoing_carrier, 
      SUM (call_duration)/60 AS Minutes, 
      SUM (call_count) AS attempt, 
      SUM (answered_count) AS answered, 
      SUM (seizure_count) AS seizure, 
      SUM(start_call_count) AS Count_X, 
      SUM(ner_count) AS NER_COUNT,
      SUM(SUM (call_duration)/60) 
      OVER (PARTITION BY B_NUMBER_COUNTRY) AS sumMinutes 
   FROM bm_archived_cdr 
   WHERE call_direction = 'O' 
     AND call_date = DATE '2016-04-21' 
   GROUP BY b_number_country, interval_of_day, outgoing_carrier
 ) dt;