Somar dados de duas tabelas

[leandroauler 0]

Galera,

 

Queria somar a quantidade de cod 100 da tabela Pass com os cod 100 da tabela Pass100...

 

Código:

 

(SELECT

DISTINCT(Aplicacao) AS APL,

COUNT(Idaut) AS Pass,

IdGuia,

Carro,

valor,

turno

FROM pass

WHERE

Idguia = 63206

GROUP BY Aplicacao)

UNION

(SELECT

DISTINCT(gp.Aplicacao) AS APL,

COUNT(gp.Idaut) AS Pass,

gp.IdGuia,

gp.Carro,

gp.valor,

gp.turno

FROM pass100 gp

WHERE

gp.Idguia = 63206

GROUP BY Aplicacao)

 

 

Resultado:

 

"APL" "Pass" "IdGuia" "Carro" "valor" "turno"

"100" 172 63206 "48004" "220" "1"

"100" 10 63206 "48004" "220" "1"

"400" 133 63206 "48004" "220" "1"

"450" 14 63206 "48004" "0" "1"

"600" 3 63206 "48004" "0" "1"

"610" 4 63206 "48004" "0" "1"

"611" 1 63206 "48004" "0" "1"

"700" 3 63206 "48004" "0" "1"

"800" 36 63206 "48004" "0" "1"

"900" 15 63206 "48004" "0" "1"

"910" 6 63206 "48004" "0" "1"

 

Ao invés de repitir duas vezes:

 

"APL" "Pass" "IdGuia" "Carro" "valor" "turno"

"100" 172 63206 "48004" "220" "1"

"100" 10 63206 "48004" "220" "1"

 

Somar os resultados:

 

"APL" "Pass" "IdGuia" "Carro" "valor" "turno"

"100" 182 63206 "48004" "220" "1"

 

Alguém sabe como ?

[Motta 645]

Tente :

 

SELECT APL,CARRO,TURNO,VALOR,SUM(PASS) PASS
FROM
(
(SELECT
DISTINCT(Aplicacao) AS APL,
COUNT(Idaut) AS Pass,
IdGuia,
Carro,
valor,
turno
FROM pass
WHERE
Idguia = 63206
GROUP BY Aplicacao)
UNION
(SELECT
DISTINCT(gp.Aplicacao) AS APL,
COUNT(gp.Idaut) AS Pass,
gp.IdGuia,
gp.Carro,
gp.valor,
gp.turno
FROM pass100 gp
WHERE
gp.Idguia = 63206
GROUP BY Aplicacao)
)
GROUP BY APL,CARRO,TURNO,VALOR

[leandroauler 0]

Tente :

 

SELECT APL,CARRO,TURNO,VALOR,SUM(PASS) PASS
FROM
(
(SELECT
DISTINCT(Aplicacao) AS APL,
COUNT(Idaut) AS Pass,
IdGuia,
Carro,
valor,
turno
FROM pass
WHERE
Idguia = 63206
GROUP BY Aplicacao)
UNION
(SELECT
DISTINCT(gp.Aplicacao) AS APL,
COUNT(gp.Idaut) AS Pass,
gp.IdGuia,
gp.Carro,
gp.valor,
gp.turno
FROM pass100 gp
WHERE
gp.Idguia = 63206
GROUP BY Aplicacao)
)
GROUP BY APL,CARRO,TURNO,VALOR

Motta,

 

Está dando o seguinte erro:

 

#1248 - Cada tabela derivada deve ter seu próprio alias

[Motta 645]

#1248 - Cada tabela derivada deve ter seu próprio alias

 

 

SELECT APL,CARRO,TURNO,VALOR,SUM(PASS) PASS
FROM
(
(SELECT
DISTINCT(Aplicacao) AS APL,
COUNT(Idaut) AS Pass,
IdGuia,
Carro,
valor,
turno
FROM pass
WHERE
Idguia = 63206
GROUP BY Aplicacao) alias1
UNION
(SELECT
DISTINCT(gp.Aplicacao) AS APL,
COUNT(gp.Idaut) AS Pass,
gp.IdGuia,
gp.Carro,
gp.valor,
gp.turno
FROM pass100 gp
WHERE
gp.Idguia = 63206
GROUP BY Aplicacao) alias2
) alias3
GROUP BY APL,CARRO,TURNO,VALOR

[leandroauler 0]

#1248 - Cada tabela derivada deve ter seu próprio alias

 

 

SELECT APL,CARRO,TURNO,VALOR,SUM(PASS) PASS
FROM
(
(SELECT
DISTINCT(Aplicacao) AS APL,
COUNT(Idaut) AS Pass,
IdGuia,
Carro,
valor,
turno
FROM pass
WHERE
Idguia = 63206
GROUP BY Aplicacao) alias1
UNION
(SELECT
DISTINCT(gp.Aplicacao) AS APL,
COUNT(gp.Idaut) AS Pass,
gp.IdGuia,
gp.Carro,
gp.valor,
gp.turno
FROM pass100 gp
WHERE
gp.Idguia = 63206
GROUP BY Aplicacao) alias2
) alias3
GROUP BY APL,CARRO,TURNO,VALOR

Motta,

 

Assim funcionou:

SELECT APL,CARRO,TURNO,VALOR,SUM(PASS) PASS
FROM
(SELECT 
DISTINCT(Aplicacao) AS APL,
COUNT(Idaut) AS Pass,
IdGuia,
Carro,
valor,
turno
FROM guiaspassageiros
WHERE
Idguia = 63206
GROUP BY 
Aplicacao
UNION
SELECT
DISTINCT(gp.Aplicacao) AS APL1,
COUNT(gp.Idaut) AS Pass,
gp.IdGuia,
gp.Carro,
gp.valor,
gp.turno
FROM guiaspassageiros100 gp 
WHERE 
gp.Idguia = 63206 
GROUP BY 
Aplicacao)a
GROUP BY APL,CARRO,TURNO,VALOR

Porem o resultado foi esse, pegou os dados somente da tabela pass100:

 

"100";"48004";"1";"220";172

E teria que ser assim:

"APL" "Pass" "IdGuia" "Carro" "valor" "turno"
"100" 182 63206 "48004" "220" "1"
"400" 133 63206 "48004" "220" "1"
"450" 14 63206 "48004" "0" "1"
"600" 3 63206 "48004" "0" "1"
"610" 4 63206 "48004" "0" "1"
"611" 1 63206 "48004" "0" "1"
"700" 3 63206 "48004" "0" "1"
"800" 36 63206 "48004" "0" "1"
"900" 15 63206 "48004" "0" "1"
"910" 6 63206 "48004" "0" "1"

[Motta 645]

tente com

PASS,SUM(VALOR) VALOR

lugar de

VALOR,SUM(PASS) PASS