>Hallo, > I have two queries: > >First (results: ANNO, IMPONIBILE): >select > EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, > SUM(DC.IMPORTO) AS IMPONIBILE >from > DOC_CORPO DC, > DOC_TESTA DT, > VOCI V >WHERE > DT.ID = DOC_TESTA_ID > AND DT.DOCUMENTO_ID <> 'PRO' > AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013 > AND DC.VOCE_ID = V.ID > AND V.CONTRIBUTI = 1 >GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) > >Second (results: ANNO, IMPOSTA): >select > EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, > SUM(DC.IMPORTO) AS IMPOSTA >from > DOC_CORPO DC, > DOC_TESTA DT >WHERE > DT.ID = DOC_TESTA_ID > AND DT.DOCUMENTO_ID <> 'PRO' > AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013 > AND DC.VOCE_ID = 'ENPACL' >GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) > >There is a way to merge those two queries to obtain the same result in >one query that results: ANNO, IMPONIBILE, IMPOSTA?
Hi, Luigi! You could try something like (I'm assuming that DOC_TESTA_ID belongs to DC and that ID is the primary key of VOCI): select EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, SUM(IIF(V.ID is not null, DC.IMPORTO, null)) AS IMPONIBILE, --sum only those that are in V SUM(IIF(DC.VOCE_ID = 'ENPACL', DC.IMPORTO, null)) AS IMPOSTA --sum only those that have correct DC.VOCE_ID from DOC_CORPO DC join DOC_TESTA DT on DC.DOC_TESTA_ID = DT.ID left join VOCI V on DC.VOCE_ID = V.ID and V.CONTRIBUTI = 1 --assuming 0 or 1 match where DT.DOCUMENTO_ID <> 'PRO' and DT.DATA_DOCUMENTO between '2013-01-01' and '2013-12-31' --BETWEEN can use an index, EXTRACT cannot and (DC.VOCE_ID = 'ENPACL' or V.ID is not null) --to avoid all rows that doesn't fit either of the requirements group by EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) HTH, Set
