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

Analytics and grouping don't tend to work together. If you perform the grouping first then do the analytic query against that 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  

Answer 2

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