ORA-30483: window functions are not allowed here

Question

Does anyone know how to get around this error? this sql works fine, but for the "cumulinvoiced" function. I am trying to add a cumulative amount, but it does not seem to work on inline views.

SELECT a.mois, a.supid, a.status, COUNT (a.status), 
       SUM (COUNT (a.status) OVER (ORDER BY a.status, a.supid, a.dossier, a.mois)) cumulinvoiced 
from (SELECT TO_CHAR (impdate, 'YYYYMM') mois, 
             benefit#sup_inv_id supid,
             SUBSTR (si_benstatus, 1, 1) status,
             files#fil_secid dossier
        FROM sbbenefit   
       WHERE benefit#sup_inv_id = 30515   
         AND SUBSTR(si_benstatus, 1, 1) = '2'
    GROUP BY TO_CHAR (impdate, 'YYYYMM'), benefit#sup_inv_id ,SUBSTR (si_benstatus, 1, 1), files#fil_secid) a 
GROUP BY a.mois, a.supid, a.status 
order BY a.supid, a.mois , a.status  

Answer 1

The aggregate COUNTshould be an analytical argument SUM, and not vice versa:

SELECT a.mois, a.supid, a.status, COUNT (a.status), 
       SUM (COUNT (a.status)) OVER (ORDER BY a.status, a.supid, a.dossier, a.mois) cumulinvoiced 
from (SELECT TO_CHAR (impdate, 'YYYYMM') mois, 
             benefit#sup_inv_id supid,
             SUBSTR (si_benstatus, 1, 1) status,
             files#fil_secid dossier
        FROM sbbenefit   
       WHERE benefit#sup_inv_id = 30515   
         AND SUBSTR(si_benstatus, 1, 1) = '2'
    GROUP BY TO_CHAR (impdate, 'YYYYMM'), benefit#sup_inv_id ,SUBSTR (si_benstatus, 1, 1), files#fil_secid) a 
GROUP BY a.mois, a.supid, a.status 
order BY a.supid, a.mois , a.status  

Answer 2

Analytics and grouping do not work together. If you do the grouping first, then do an analytic query with this result, I think it should work:

SELECT mois, supid, status, status_count,
      SUM (status_count OVER (ORDER BY a.status, a.supid, a.dossier, a.mois)) cumulinvoiced 
from
(SELECT  a.mois, a.supid, a.status, COUNT (a.status) status_count
from 
(
SELECT  
 TO_CHAR (impdate, 'YYYYMM') mois, 
 benefit#sup_inv_id supid,
 SUBSTR (si_benstatus, 1, 1) status,
 files#fil_secid dossier
FROM sbbenefit   
WHERE  benefit#sup_inv_id = 30515   
 AND  SUBSTR (si_benstatus, 1, 1) = '2'
GROUP BY 
 TO_CHAR (impdate, 'YYYYMM'), 
 benefit#sup_inv_id ,
 SUBSTR (si_benstatus, 1, 1),
 files#fil_secid  
) a 
GROUP BY 
a.mois, a.supid, a.status
) 
order BY 
supid, mois , status