Falta columna suma

Hola EXPERTO buen dia tengo este codigo pero necesito crear el total de forma horizontal y vertical y que se muestre de esta manera muchas gracias:
producto enero febreto marzo ..... Total
televisor 20        50            40         110
dvd        10        60             10           80
total       30       110           50           190
select pro_producto,;
sum(iif(PF_MES=1,1,0)) as MES1,;
sum(iif(PF_MES=2,1,0)) as MES2,;
sum(iif(PF_MES=3,1,0)) as MES3,;
sum(iif(PF_MES=4,1,0)) as MES4,;
sum(iif(PF_MES=5,1,0)) as MES5,;
sum(iif(PF_MES=6,1,0)) as MES6,;
sum(iif(PF_MES=7,1,0)) as MES7,;
sum(iif(PF_MES=8,1,0)) as MES8,;
sum(iif(PF_MES=9,1,0)) as MES9,;
sum(iif(PF_MES=10,1,0)) as MES10,;
sum(iif(PF_MES=11,1,0)) as MES11,;
sum(iif(PF_MES=12,1,0)) as MES12 ;
from ventas;
group by ventas.pro_producto;
order by pro_prodcuto into cursor xx

1 respuesta

Respuesta
1
Bueno esto no puedes hacerlo en SQL directamente en forma vertical, generalmente esto se tira a un report y luego se suma las columnas, pero podrias hacer lo siguiente.
select pro_producto,;
sum(iif(PF_MES=1,1,0)) as MES1,;
sum(iif(PF_MES=2,1,0)) as MES2,;
sum(iif(PF_MES=3,1,0)) as MES3,;
sum(iif(PF_MES=4,1,0)) as MES4,;
sum(iif(PF_MES=5,1,0)) as MES5,;
sum(iif(PF_MES=6,1,0)) as MES6,;
sum(iif(PF_MES=7,1,0)) as MES7,;
sum(iif(PF_MES=8,1,0)) as MES8,;
sum(iif(PF_MES=9,1,0)) as MES9,;
sum(iif(PF_MES=10,1,0)) as MES10,;
sum(iif(PF_MES=11,1,0)) as MES11,;
sum(iif(PF_MES=12,1,0)) as MES12 ;
from ventas;
group by ventas.pro_producto;
Order by pro_prodcuto into cursor xx READWRITE
sele xx
calculate sum(mes1) to xTot1
calculate sum(mes2) to xTot2
calculate sum(mes3) to xTot3
calculate sum(mes4) to xTot4
calculate sum(mes5) to xTot5
calculate sum(mes6) to xTot6
calculate sum(mes7) to xTot7
calculate sum(mes8) to xTot8
calculate sum(mes9) to xTot9
calculate sum(mes10) to xTot10
calculate sum(mes11) to xTot11
calculate sum(mes12) to xTot12
append blank
replace producto with 'Z -- TOTALES'
replace mes1 with xTot1, mes2 with xTot2, mes3 with xTot3, mes4 with xTot4, mes5 with xTot5, mes6 with xTot6, mes7 with xTot7, mes8 with xTot8, mes9 with xTot9, mes10 with xTot10, mes11 with xTot11, mes12  with xTot12
y listo, pero este codigo solo funcionara a partir de vfp7 debido a la funcon READWRITE, si usas una version inferior avisame para ver un truco de convertir un cursor a lectura escritura.
PD: El codigo puede tener errores ya que lo escribi directamente aqui sin probarlo en vfp, algun problema me avisas.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas