Ir para conteúdo

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

alexjose123

Como somar dois ou mais counts?

Por , em Banco de Dados

Recommended Posts

alexjose123    0

alexjose123
Postado

Tenho dois select unidos e quero a soma dos resultados individuais. De que forma posso fazer isso.

Alguma sugestão.



SELECT count (DISTINCT P.CHTIPOPROCESSO)
FROM PROCESSO P
 INNER JOIN TIPOPROCESSO        TP ON TP.CHTIPOPROCESSO = P.CHTIPOPROCESSO
 LEFT OUTER JOIN PROCESSO P1 ON P1.CHPROCESSO       	= P.CHPROCESSOPAI
 INNER JOIN HISTORICO       	H  ON H.CHPROCESSO      = P.CHPROCESSO
 INNER JOIN HISTORICO       	H1 ON H1.CHPROCESSO 	= P.CHPROCESSO
 INNER JOIN DISTRIBUICAO        D  ON D.CHPROCESSO      = P.CHPROCESSO
AND D.CHTIPODISTRIBUICAO IN (1,2,11,12)
 INNER JOIN LOCALIDADES     	L  ON L.CHESTRANGEIRA   = D.CHTURMA AND
L.TIPOLOCALIDADE = 'U'
 INNER JOIN JUIZ                J  ON J.CHJUIZ          = P.CHJUIZRELATOR
WHERE (TP.ID_CNJ IN
(178,180,181,182,183,1723,186,1726,190,1725,193,191,196,47,37,110,120,119,976,985,987,988,1202,307,241,1145,216,231,232,233,236,1230,1231,1232)
   OR TP.ID_CNJ IN (1001,1003,1004,1267,1009,1685)
   OR (TP.ID_CNJ = 1005 AND ((P.VARA_CNJ = '0000' AND P.NUMERACAO_CNJ <>
P1.NUMERACAO_CNJ) OR (P.VARA_CNJ = '0000' AND P1.VARA_CNJ <>
'0000'))))
 AND H1.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
HH24:MI:SS') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY HH24:MI:SS')
 AND H.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
HH24:MI:SS') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY HH24:MI:SS')
 AND H.CHTIPOHISTORICO IN
(631,629,633,635,606,577,442,634,632,489,163,630,65,609,1624,1609)
 AND H1.CHTIPOHISTORICO IN
(197,375,551,550,569,557,552,485,364,367,368,111,113,116,156,147,168,141,74,70,142,94,73,
                        	232,181,352,205,527,182,208,369,319,374,118,155,117,32,353,376,373,372,386,385,139,63,263,112,
                        	119,235,237,236,238,86,60,53,61,54,62,416,148,149,75,7,204,107,106,104,105,109,108,294,302,303,321,81,323,
                        	304,306,307,325,324,326,318,308,310,309,311,312,313,314,317,315,316,327,328,329,330,332,331,378,529,530,531,
                        	160,206,31,47,344,34,43,320,210,535,534,528,533,526,233,138,77,532,502,51,33,275,
                        	1642,2004,2005,2016,2017,2018,2021,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,
                        	2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,
                        	2103,2104,2105,2106,2107)
 AND H.CHPROCESSO = H1.CHPROCESSO
 AND H1.CHTIPOTRANSACAO = 'PAUJ'
 AND H1.DATAHISTORICO > H.DATAHISTORICO

union all

/* 2.193 - Recursos Internos julgados em sessão*/
SELECT count (DISTINCT P.CHTIPOPROCESSO)
FROM PROCESSO P
 INNER JOIN TIPOPROCESSO        TP ON TP.CHTIPOPROCESSO = P.CHTIPOPROCESSO
 LEFT OUTER JOIN PROCESSO   	P1 ON P1.CHPROCESSO 	= P.CHPROCESSOPAI
 INNER JOIN DISTRIBUICAO        D  ON D.CHPROCESSO      = P.CHPROCESSO
AND D.CHTIPODISTRIBUICAO IN (1,2,11,12)
 INNER JOIN HISTORICO       	H  ON H.CHPROCESSO      = P.CHPROCESSO
 INNER JOIN LOCALIDADES     	L  ON L.CHESTRANGEIRA   = D.CHTURMA AND
L.TIPOLOCALIDADE = 'U'
 INNER JOIN JUIZ                J  ON J.CHJUIZ          = P.CHJUIZRELATOR
WHERE ((TP.ID_CNJ IN (1000, 1689)) OR (TP.ID_CNJ = 1005 AND
P.NUMERACAO_CNJ = P1.NUMERACAO_CNJ))
 AND H.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
hh24:mi:ss') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY hh24:mi:ss')
 AND H.CHTIPOHISTORICO IN
(306,230,167,62,2017,109,2016,233,108,138,139,107,204,302,373,319,597,363,83,
                           375,485,181,527,369,531,526,502,51,1642,552,368,33,364,34,43,295,168,74,550,
                           1642,337,362,569,557,352,371,106,32,197,75,328,70,215,2071,2072,2073,2074,2075,
                           2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,
                           2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,2103,2104,2105,2106,2107)
 AND H.CHTIPOTRANSACAO = 'PAUJ'
 AND
 EXISTS
	(SELECT H2.CHTIPOHISTORICO
     FROM   HISTORICO H2
     WHERE H2.CHPROCESSO = P.CHPROCESSO
       AND H2.CHTIPOHISTORICO IN
(631,629,633,635,606,577,442,634,632,489,163,57,630,65,609,1624,1609))

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

Se o BD aceita tabela virtual

select sum(qtd) qtdtot
from 
(
SELECT count (DISTINCT P.CHTIPOPROCESSO) qtd
FROM PROCESSO P
 INNER JOIN TIPOPROCESSO        TP ON TP.CHTIPOPROCESSO = P.CHTIPOPROCESSO
 LEFT OUTER JOIN PROCESSO P1 ON P1.CHPROCESSO          = P.CHPROCESSOPAI
 INNER JOIN HISTORICO          H  ON H.CHPROCESSO      = P.CHPROCESSO
 INNER JOIN HISTORICO          H1 ON H1.CHPROCESSO     = P.CHPROCESSO
 INNER JOIN DISTRIBUICAO        D  ON D.CHPROCESSO      = P.CHPROCESSO
AND D.CHTIPODISTRIBUICAO IN (1,2,11,12)
 INNER JOIN LOCALIDADES        L  ON L.CHESTRANGEIRA   = D.CHTURMA AND
L.TIPOLOCALIDADE = 'U'
 INNER JOIN JUIZ                J  ON J.CHJUIZ          = P.CHJUIZRELATOR
WHERE (TP.ID_CNJ IN
(178,180,181,182,183,1723,186,1726,190,1725,193,191,196,47,37,110,120,119,976,985,987,988,1202,307,241,1145,216,231,232,233,236,1230,1231,1232)
   OR TP.ID_CNJ IN (1001,1003,1004,1267,1009,1685)
   OR (TP.ID_CNJ = 1005 AND ((P.VARA_CNJ = '0000' AND P.NUMERACAO_CNJ <>
P1.NUMERACAO_CNJ) OR (P.VARA_CNJ = '0000' AND P1.VARA_CNJ <>
'0000'))))
 AND H1.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
HH24:MI:SS') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY HH24:MI:SS')
 AND H.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
HH24:MI:SS') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY HH24:MI:SS')
 AND H.CHTIPOHISTORICO IN
(631,629,633,635,606,577,442,634,632,489,163,630,65,609,1624,1609)
 AND H1.CHTIPOHISTORICO IN
(197,375,551,550,569,557,552,485,364,367,368,111,113,116,156,147,168,141,74,70,142,94,73,
                               232,181,352,205,527,182,208,369,319,374,118,155,117,32,353,376,373,372,386,385,139,63,263,112,
                               119,235,237,236,238,86,60,53,61,54,62,416,148,149,75,7,204,107,106,104,105,109,108,294,302,303,321,81,323,
                               304,306,307,325,324,326,318,308,310,309,311,312,313,314,317,315,316,327,328,329,330,332,331,378,529,530,531,
                               160,206,31,47,344,34,43,320,210,535,534,528,533,526,233,138,77,532,502,51,33,275,
                               1642,2004,2005,2016,2017,2018,2021,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,
                               2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,
                               2103,2104,2105,2106,2107)
 AND H.CHPROCESSO = H1.CHPROCESSO
 AND H1.CHTIPOTRANSACAO = 'PAUJ'
 AND H1.DATAHISTORICO > H.DATAHISTORICO

union all

/* 2.193 - Recursos Internos julgados em sessão*/
SELECT count (DISTINCT P.CHTIPOPROCESSO) qtd
FROM PROCESSO P
 INNER JOIN TIPOPROCESSO        TP ON TP.CHTIPOPROCESSO = P.CHTIPOPROCESSO
 LEFT OUTER JOIN PROCESSO      P1 ON P1.CHPROCESSO     = P.CHPROCESSOPAI
 INNER JOIN DISTRIBUICAO        D  ON D.CHPROCESSO      = P.CHPROCESSO
AND D.CHTIPODISTRIBUICAO IN (1,2,11,12)
 INNER JOIN HISTORICO          H  ON H.CHPROCESSO      = P.CHPROCESSO
 INNER JOIN LOCALIDADES        L  ON L.CHESTRANGEIRA   = D.CHTURMA AND
L.TIPOLOCALIDADE = 'U'
 INNER JOIN JUIZ                J  ON J.CHJUIZ          = P.CHJUIZRELATOR
WHERE ((TP.ID_CNJ IN (1000, 1689)) OR (TP.ID_CNJ = 1005 AND
P.NUMERACAO_CNJ = P1.NUMERACAO_CNJ))
 AND H.DATAHISTORICO BETWEEN TO_DATE('01/01/2010 00:00:00','DD/MM/YYYY
hh24:mi:ss') AND TO_DATE('31/01/2010 23:59:59','DD/MM/YYYY hh24:mi:ss')
 AND H.CHTIPOHISTORICO IN
(306,230,167,62,2017,109,2016,233,108,138,139,107,204,302,373,319,597,363,83,
                           375,485,181,527,369,531,526,502,51,1642,552,368,33,364,34,43,295,168,74,550,
                           1642,337,362,569,557,352,371,106,32,197,75,328,70,215,2071,2072,2073,2074,2075,
                           2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,
                           2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,2103,2104,2105,2106,2107)
 AND H.CHTIPOTRANSACAO = 'PAUJ'
 AND
 EXISTS
       (SELECT H2.CHTIPOHISTORICO
     FROM   HISTORICO H2
     WHERE H2.CHPROCESSO = P.CHPROCESSO
       AND H2.CHTIPOHISTORICO IN
(631,629,633,635,606,577,442,634,632,489,163,57,630,65,609,1624,1609))
) virtual

Compartilhar este post

Link para o post
Compartilhar em outros sites
Ir para a lista de tópicos Banco de Dados
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.

  Aceito