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