Erro no UNION

Question

 

Bom dia colegas!

Estou fazendo uma quey que resgata os vendedores e a soma de produtos vendidos de cada um (em R$).

Só que preciso inserir uma linha no final da query, pra exibir o total de todos os vendedores listados.

Estou usando o UNION e apresenta o seguinte erro:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

 

A query está abaixo:

 

SELECT ((SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

ON T0.DocEntry = T2.DocEntry

WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

GROUP BY T1.SlpName

--ORDER BY SUM(T0.DocTotal) desc

)

UNION

(Select T1.SlpName, SUM(T0.DocTotal) as Soma

FROM OINV T0))

 

O resultado esperado seria :

 

Carlos	10.000,00
Alberto	8.400,00
Luis	6.000,00
Amélia	3.000,00
	27.400,00

Desde já agradeço.

Answer 1

 

segue uma alteranativa

 

Select SlpName, teste
From (

SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste
FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2
ON T0.DocEntry = T2.DocEntry
WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'
GROUP BY T1.SlpName
UNION ALL
Select 'Z-Total', SUM(T0.DocTotal) as Soma FROM OINV T0

) Tbl Order By SlpName

 

 

 

Abs/;

Answer 2

 

Riian,

 

Tire os parenteses

 

SELECT * from

(SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

ON T0.DocEntry = T2.DocEntry

WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

GROUP BY T1.SlpName

 

UNION

Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

FROM OINV T0

) a

 

 

abs

 

Rafael Krisller

Answer 3

Bom dia,

 

Senhores, muito obrigado pela ajuda.

Essa solução dada pelo Rafael, trouxe o resultado esperado.

Agora só outra pergunta .... 

Gostaria de ordenar de forma decrescente os valores, colokei um order by mas dá o seguinte o erro:

 

Msg 1033, Level 15, State 1, Line 16

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Msg 102, Level 15, State 1, Line 22

Incorrect syntax near ')'.

 

 

A query que eu alterei foi essa: (A linha alterada está em negrito e sublinhada)

 

SELECT * from

(SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as teste

FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

ON T0.DocEntry = T2.DocEntry

WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

GROUP BY T1.SlpName

ORDER BY SUM(T0.DocTotal) desc

UNION

Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

FROM OINV T0

) a

 

Mais uma vez, obrigado!

 

Att,

 

Answer 4

Riian,

 

Coloque o Order by fora do union, ou seja, no select principal

 

abs

Answer 5

 

Code Snippet

SELECT * from

(SELECT T1.SlpName AS Vendedor, SUM(T0.DocTotal) as Soma

FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2

ON T0.DocEntry = T2.DocEntry

WHERE T0.DocDate >= '20080418' AND T0.DocDate <= '20080418' AND T2.LineStatus = 'O'

GROUP BY T1.SlpName

 

UNION

Select ' ' as SlpName, SUM(T0.DocTotal) as Soma

FROM OINV T0

) a

 

ORDER BY a.soma desc

 

 

 

Answer 6

Rafael,

 

Deu certo!

Mto obrigado pelo help!!

 

Abs