usar doble group by

[Libras]

Iniciado por damimg

Traté de hacerlo pero se complica bastante, me parece que está lejos de usar Union!

como que no sale con union ;)

Código SQL:

CREATE TABLE #temp(
vendedor VARCHAR(20),
monto INT
)
 
INSERT INTO #temp VALUES ('vendedor1',1000)
INSERT INTO #temp VALUES ('vendedor2',2000)
INSERT INTO #temp VALUES ('vendedor3',3000)
INSERT INTO #temp VALUES ('vendedor4',4000)
INSERT INTO #temp VALUES ('vendedor5',5000)
INSERT INTO #temp VALUES ('vendedor6',6000)
INSERT INTO #temp VALUES ('vendedor7',7000)
INSERT INTO #temp VALUES ('vendedor8',8000)
INSERT INTO #temp VALUES ('vendedor9',9000)
INSERT INTO #temp VALUES ('vendedor10',10000)
INSERT INTO #temp VALUES ('vendedor11',11000)
INSERT INTO #temp VALUES ('vendedor12',12000)
INSERT INTO #temp VALUES ('vendedor13',13000)
INSERT INTO #temp VALUES ('vendedor14',14000)
INSERT INTO #temp VALUES ('vendedor15',15000)
 
 
 
SELECT 'otros' vendedor, SUM(otros) monto FROM(
SELECT CASE WHEN vendedor NOT IN (SELECT top 10 vendedor FROM #temp ORDER BY monto DESC) THEN SUM(monto) END AS otros FROM #temp
GROUP BY vendedor) t1
UNION
SELECT * FROM
(SELECT top 10 vendedor,monto FROM #temp ORDER BY monto DESC)t2
 

Saludos!