Como agrupar una columna subselect ? alias ?

m1di · #1 (**permalink**) 11/04/2012, 10:42

Hola amigos estoy tratando de agrupar un alias pero no encuentro como hacerlo.. me gustaria por favor puedan ayudarme les gradeceria.

a continuacion les muestro el codigo.

select '', '' AS MES,

(select top 1 case
WHEN RTRIM(caff.SALSTERR) between '10000' and '19999' THEN 'LA GRAN CARACAS'
WHEN RTRIM(caff.SALSTERR) between '20000' and '29999' THEN 'ZULIA-FALCON'
WHEN RTRIM(caff.SALSTERR) between '30000' and '39999' THEN 'CENTRO'
WHEN RTRIM(caff.SALSTERR) between '40000' and '49999' THEN 'LOS ANDES'
WHEN RTRIM(caff.SALSTERR) between '50000' and '59999' THEN 'LARA - LOS LLANOS'
WHEN RTRIM(caff.SALSTERR) between '60000' and '69999' THEN 'ORIENTE'
WHEN RTRIM(caff.SALSTERR) = '70000' THEN 'OFICINA'
end as ZONA FRom [SOP30200] caff where caff.SALSTERR = caf.SALSTERR group by caff.SALSTERR) as ZONA,

0 unidades_total,

cast(isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SALSTERR = caf.SALSTERR
and fIn.SOPTYPE='3'
and fIn.SALSTERR between '10000' and '70000'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) -

isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SALSTERR = caf.SALSTERR
and fIn.SOPTYPE='4'
and fIn.SALSTERR between '10000' and '70000'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) as int) TOTAL

from SOP30200 caf
inner join SOP30300 de on caf.SOPNUMBE = de.SOPNUMBE

where
caf.VOIDSTTS<>'1'
and de.[cmpntseq] not in ('16384','32768')
and (caf.SOPTYPE = '3' or caf.SOPTYPE = '4')
and( caf.SALSTERR between between '10000' and '79999')
and (caf.DOCDATE between '01/01/2011' and '31/12/2011')

group by caf.SALSTERR

el resultado q me da en la columna ZONA es el siguiente.
CENTRO
CENTRO
AMERICA
ZULIA
ZULIA

quisiera q apareciece
CENTRO
AMERICA
ZULIA

Gracias y espero puedan ayudarme.

Libras · #2 (**permalink**) 11/04/2012, 16:06

Código SQL:

Ver originalSELECT * FROM(
 
SELECT '', '' AS MES,
 
(SELECT top 1 CASE
WHEN RTRIM(caff.SALSTERR) BETWEEN '10000' AND '19999' THEN 'LA GRAN CARACAS'
WHEN RTRIM(caff.SALSTERR) BETWEEN '20000' AND '29999' THEN 'ZULIA-FALCON'
WHEN RTRIM(caff.SALSTERR) BETWEEN '30000' AND '39999' THEN 'CENTRO'
WHEN RTRIM(caff.SALSTERR) BETWEEN '40000' AND '49999' THEN 'LOS ANDES'
WHEN RTRIM(caff.SALSTERR) BETWEEN '50000' AND '59999' THEN 'LARA - LOS LLANOS'
WHEN RTRIM(caff.SALSTERR) BETWEEN '60000' AND '69999' THEN 'ORIENTE'
WHEN RTRIM(caff.SALSTERR) = '70000' THEN 'OFICINA'
END AS ZONA FROM [SOP30200] caff WHERE caff.SALSTERR = caf.SALSTERR GROUP BY caff.SALSTERR) AS ZONA,
 
 
0 unidades_total,
 
CAST(isnull(((SELECT top 1 SUM(quantity) FROM [SOP30300] d
INNER JOIN [SOP30200] fIn ON d.SOPNUMBE = fIn.SOPNUMBE
WHERE fIn.VOIDSTTS<>1
AND fIn.SALSTERR = caf.SALSTERR
AND fIn.SOPTYPE='3'
AND fIn.SALSTERR BETWEEN '10000' AND '70000'
AND fIn.DOCDATE BETWEEN '01/01/2011' AND '31/12/2011'
AND d.[cmpntseq] NOT IN ('16384','32768'))),0) -
 
isnull(((SELECT top 1 SUM(quantity) FROM [SOP30300] d
INNER JOIN [SOP30200] fIn ON d.SOPNUMBE = fIn.SOPNUMBE
WHERE fIn.VOIDSTTS<>1
AND fIn.SALSTERR = caf.SALSTERR
AND fIn.SOPTYPE='4'
AND fIn.SALSTERR BETWEEN '10000' AND '70000'
AND fIn.DOCDATE BETWEEN '01/01/2011' AND '31/12/2011'
AND d.[cmpntseq] NOT IN ('16384','32768'))),0) AS INT) TOTAL
 
 
FROM SOP30200 caf
INNER JOIN SOP30300 de ON caf.SOPNUMBE = de.SOPNUMBE
 
WHERE
caf.VOIDSTTS<>'1'
AND de.[cmpntseq] NOT IN ('16384','32768')
AND (caf.SOPTYPE = '3' OR caf.SOPTYPE = '4')
AND( caf.SALSTERR BETWEEN BETWEEN '10000' AND '79999')
AND (caf.DOCDATE BETWEEN '01/01/2011' AND '31/12/2011')
 
GROUP BY caf.SALSTERR) AS t1 GROUP BY mes

algo asi :)