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' partitioning function. i'm trying to add a cumulative total, but it doesn't 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 COUNT should be an argument to the analytic SUM, 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