Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

union all + group by

Estas en el tema de union all + group by en el foro de Mysql en Foros del Web. quiero unir 6 consultas (que individualmente funcionan bien) en una sola consulta, pero solo muestra los resultados del primer select. Código: select t.a, t.normals, t.sumneto, ...
  #1 (permalink)  
Antiguo 28/11/2017, 07:25
 
Fecha de Ingreso: agosto-2002
Ubicación: Lleida
Mensajes: 503
Antigüedad: 17 años, 2 meses
Puntos: 0
union all + group by

quiero unir 6 consultas (que individualmente funcionan bien) en una sola consulta, pero solo muestra los resultados del primer select.


Código:
select t.a, t.normals, t.sumneto, t.sumdto, t.vtamedia,    
		t.garantia, t.sumnetgar, t.sumdtogar, t.vtamediagar,
		t.errores,  t.sumneterr, t.sumdtoerr, t.vtamediaerr,
		t.reparacions,  t.sumnetrep, t.sumdtorep, t.vtamediarep,
		t.promo1, t.sumnetpromo1, t.sumdtopromo1, t.vtamediapromo1,
		t.promo2, t.sumnetpromo2, t.sumdtopromo2, t.vtamediapromo2 
from
(
select encargos.vendedorVta as a, count(*) as normals, sum(encargos.impnetoVta) as sumneto, sum(encargos.impdtoVta) as sumdto, avg(encargos.impnetoVta) as vtamedia,    
	0 as garantia, 0 as sumnetgar, 0 as sumdtogar, 0 as vtamediagar,
	0 as errores,  0 as sumneterr, 0 as sumdtoerr, 0 as vtamediaerr,
	0 as reparacions,  0 as sumnetrep, 0 as sumdtorep, 0 as vtamediarep,
	0 as promo1, 0 as sumnetpromo1, 0 as sumdtopromo1, 0 as vtamediapromo1,
	0 as promo2, 0 as sumnetpromo2, 0 as sumdtopromo2, 0 as vtamediapromo2
from encargos 
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta=0
group by encargos.vendedorVta
union all
select encargos.vendedorVta as a, 0 as normals, 0 as sumneto, 0 as sumdto, 0 as vtamedia,    
	count(*) as garantia, sum(encargos.impnetoVta) as sumnetgar, sum(encargos.impdtoVta) as sumdtogar, avg(encargos.impnetoVta) as vtamediagar,    
	0 as errores,  0 as sumneterr, 0 as sumdtoerr, 0 as vtamediaerr,
	0 as reparacions,  0 as sumnetrep, 0 as sumdtorep, 0 as vtamediarep,
	0 as promo1, 0 as sumnetpromo1, 0 as sumdtopromo1, 0 as vtamediapromo1,
	0 as promo2, 0 as sumnetpromo2, 0 as sumdtopromo2, 0 as vtamediapromo2
from encargos 
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta=0
group by encargos.vendedorVta
union all
select encargos.vendedorVta as a,  0 as normals, 0 as sumneto, 0 as sumdto, 0 as vtamedia,   									 
        0 as garantia, 0 as sumnetgar, 0 as sumdtogar, 0 as vtamediagar,    
	count(*) as errores, sum(encargos.impnetoVta) as sumneterr, sum(encargos.impdtoVta) as sumdtoerr, avg(encargos.impnetoVta) as vtamediaerr,
  	0 as reparacions,  0 as sumnetrep, 0 as sumdtorep, 0 as vtamediarep,
 	0 as promo1, 0 as sumnetpromo1, 0 as sumdtopromo1, 0 as vtamediapromo1,
	0 as promo2, 0 as sumnetpromo2, 0 as sumdtopromo2, 0 as vtamediapromo2
from encargos  
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta=3
group by encargos.vendedorVta
union all
select encargos.vendedorVta as a,  0 as normals, 0 as sumneto, 0 as sumdto, 0 as vtamedia,    
	 0 as garantia, 0 as sumnetgar, 0 as sumdtogar, 0 as vtamediagar,    
	 0 as errores, 0 as sumneterr, 0 as sumdtoerr, 0 as vtamediaerr,
	 count(*) as reparacions,  sum(encargos.impnetoVta) as sumnetrep, sum(encargos.impdtoVta) as sumdtorep, avg(encargos.impnetoVta) as vtamediarep,
	0 as promo1, 0 as sumnetpromo1, 0 as sumdtopromo1, 0 as vtamediapromo1,
	0 as promo2, 0 as sumnetpromo2, 0 as sumdtopromo2, 0 as vtamediapromo2

from encargos  
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta=3
group by encargos.vendedorVta
union all
select encargos.vendedorVta as a,  0 as normals, 0 as sumneto, 0 as sumdto, 0 as vtamedia,    
	 0 as garantia, 0 as sumnetgar, 0 as sumdtogar, 0 as vtamediagar,    
	 0 as errores, 0 as sumneterr, 0 as sumdtoerr, 0 as vtamediaerr,
	 0 as reparacions,  0 as sumnetrep, 0 as sumdtorep, 0 as vtamediarep,
	count(*) as promo1,  sum(encargos.impnetoVta) as sumnetpromo1, sum(encargos.impdtoVta) as sumdtopromo1, avg(encargos.impnetoVta) as vtamediapromo1,
	0 as promo2, 0 as sumnetpromo2, 0 as sumdtopromo2, 0 as vtamediapromo2
from encargos  
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta>3 and encargos.encargopralVta = 'S'
group by encargos.vendedorVta
union all
select encargos.vendedorVta as a,  0 as normals, 0 as sumneto, 0 as sumdto, 0 as vtamedia,    
	 0 as garantia, 0 as sumnetgar, 0 as sumdtogar, 0 as vtamediagar,    
	 0 as errores, 0 as sumneterr, 0 as sumdtoerr, 0 as vtamediaerr,
	 0 as reparacions,  0 as sumnetrep, 0 as sumdtorep, 0 as vtamediarep,
	 0 as promo1, 0 as sumnetpromo1, 0 as sumdtopromo1, 0 as vtamediapromo1,
	count(*) as promo2,  sum(encargos.impnetoVta) as sumnetpromo2, sum(encargos.impdtoVta) as sumdtopromo2, avg(encargos.impnetoVta) as vtamediapromo2
from encargos  
where encargos.fechaVta between '2017-11-01' and '2017-12-21' and encargos.promocionVta>3 and encargos.encargopralVta = 'N'
group by encargos.vendedorVta
) t
group by t.a
order by t.a
  #2 (permalink)  
Antiguo 28/11/2017, 09:28
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.404
Antigüedad: 13 años, 2 meses
Puntos: 774
Respuesta: union all + group by

porque dices que solo muestra los resultados de un select? ya probaste quitando el group by para ver si solo regresa datos de un select?
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me

Etiquetas: group, select, union
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 12:07.