Perform the sum of an added field - PL / SQL

0

I need to sum a field to which a sum was made according to the example below:

SELECT A.*, B.* FROM

     (SELECT DTAGENDA, AG.CODITPROD, CD, ROW_NUMBER() OVER(PARTITION BY AG.CODITPROD, CD ORDER BY DTAGENDA) AS RANK,
      sum(QTAGENDA) QTD_AGENDA
      FROM ADM.MAG_T_DW_AGENDAMENTO AG
      INNER JOIN ADM.MAG_T_ARVORE_PRODUTO A ON A.CODITPROD = AG.CODITPROD
      WHERE AG.DTAGENDA >= TO_DATE(SYSDATE)  and AG.DTAGENDA <= TO_DATE(SYSDATE+180)
      AND AG.STATUS NOT IN ('CANCELADO', 'DEVOLVEU POR DIVERGÊNCIA', 'NÃO COMPARECEU', 'DESISTÊNCIA')
      GROUP BY DTAGENDA, AG.CODITPROD, CD
      ORDER BY AG.CODITPROD, CD, DTAGENDA) A,

                   (SELECT SUM(QTAGENDA) AS AGENDA_30
                    FROM ADM.MAG_T_DW_AGENDAMENTO AG
                    INNER JOIN ADM.MAG_T_ARVORE_PRODUTO A ON A.CODITPROD = AG.CODITPROD
                    WHERE DTAGENDA >= TO_DATE(SYSDATE+30)
                    GROUP BY AG.CODITPROD
                    ) B              
WHERE A.RANK = 1

However, the result obtained from the second subselect is far from expected, as shown below. link

The expected scenario would be a sum of the A.QTD_AGENDA field, but I do not know a syntax that would allow it.

NOTE: ROLLUP would not help

    
asked by anonymous 04.07.2017 / 20:34

1 answer

0

Tried:

SELECT A FROM

     (SELECT DTAGENDA, AG.CODITPROD, CD, ROW_NUMBER() OVER(PARTITION BY AG.CODITPROD, CD ORDER BY DTAGENDA) AS RANK,
      sum(QTAGENDA) QTD_AGENDA,
      SUM(QTAGENDA) OVER(PARTITION BY AG.CODITPROD, CD ORDER BY DTAGENDA) AS AGENDA_30
      FROM ADM.MAG_T_DW_AGENDAMENTO AG
      INNER JOIN ADM.MAG_T_ARVORE_PRODUTO A ON A.CODITPROD = AG.CODITPROD
      WHERE AG.DTAGENDA >= TO_DATE(SYSDATE)  and AG.DTAGENDA <= TO_DATE(SYSDATE+180)
      AND AG.STATUS NOT IN ('CANCELADO', 'DEVOLVEU POR DIVERGÊNCIA', 'NÃO COMPARECEU', 'DESISTÊNCIA')
      GROUP BY DTAGENDA, AG.CODITPROD, CD
      ORDER BY AG.CODITPROD, CD, DTAGENDA) A
WHERE A.RANK = 1
    
17.07.2017 / 16:56