none
como hallar una suma de una suma? RRS feed

  • Pregunta

  • SAludos gente!!! lo que requiero es obtener una 3ra fila que muestre los totales de cada campo de la suma de estas otras 2 filas q filtro

    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CC
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CCFA
    where cod_fuente='02' and periodo_sol between '201202' and '201202'

    como ven son sumas identicas que se hace de una y otra tabla lo que necesito es una 3ra fila que me muestre la sumatoria de estas 2 filas, campo por campo, ayuda genios.... gracias

    jueves, 6 de junio de 2013 15:01

Respuestas

  • O esto?

    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CC
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CCFA
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select sum(s1), sum(s2), sum(s3), sum(s4), sum(s5), sum(s6), sum(s7), sum(s8) from (
    select SUM(monto_sol) as s1,SUM(monto_desc_conv)as s2,
      SUM(monto_des_entidad) as s3,SUM(monto_pag_egs) as s4,
      SUM(monto_pag_com) as s5,SUM(monto_pag_pe) as s6,
      SUM(monto_pag_mh) as s7,SUM(monto_dev) as s8 from TCON_PAGO_CC
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select SUM(monto_sol) as s1,SUM(monto_desc_conv)as s2,
      SUM(monto_des_entidad) as s3,SUM(monto_pag_egs) as s4,
      SUM(monto_pag_com) as s5,SUM(monto_pag_pe) as s6,
      SUM(monto_pag_mh) as s7,SUM(monto_dev) as s8 from TCON_PAGO_CCFA
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    )


    • Marcado como respuesta Bpotro82 jueves, 6 de junio de 2013 19:31
    jueves, 6 de junio de 2013 15:12

Todas las respuestas

  • Hola:

    Has probado con WITH ROLLUP o con COMPUTE BY  ???


    Sergio Sánchez Arias
    Oaxaca,México
    AYÚDANOS A AYUDARTE

    jueves, 6 de junio de 2013 15:07
  • O esto?

    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CC
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select SUM(monto_sol) as Solicitado,SUM(monto_desc_conv)as Convenios,
      SUM(monto_des_entidad) as Desembosado,SUM(monto_pag_egs) as EGS,
      SUM(monto_pag_com) as Comisión,SUM(monto_pag_pe) as Pensión,
      SUM(monto_pag_mh) as MH,SUM(monto_dev) as Devolución from TCON_PAGO_CCFA
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select sum(s1), sum(s2), sum(s3), sum(s4), sum(s5), sum(s6), sum(s7), sum(s8) from (
    select SUM(monto_sol) as s1,SUM(monto_desc_conv)as s2,
      SUM(monto_des_entidad) as s3,SUM(monto_pag_egs) as s4,
      SUM(monto_pag_com) as s5,SUM(monto_pag_pe) as s6,
      SUM(monto_pag_mh) as s7,SUM(monto_dev) as s8 from TCON_PAGO_CC
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    union
    select SUM(monto_sol) as s1,SUM(monto_desc_conv)as s2,
      SUM(monto_des_entidad) as s3,SUM(monto_pag_egs) as s4,
      SUM(monto_pag_com) as s5,SUM(monto_pag_pe) as s6,
      SUM(monto_pag_mh) as s7,SUM(monto_dev) as s8 from TCON_PAGO_CCFA
    where cod_fuente='02' and periodo_sol between '201202' and '201202'
    )


    • Marcado como respuesta Bpotro82 jueves, 6 de junio de 2013 19:31
    jueves, 6 de junio de 2013 15:12
  • funcional solo faltaría un seudónimo para ese From agrupado y listo, pero poco ortodoxo como tendría que hacer con un  WITH ROLLUP o con COMPUTE BY ??? me quedo la duda y no entendí bien los ejemplos del enlace para este caso particular
    jueves, 6 de junio de 2013 15:22
  • Lo primero seria cambiar UNION por UNION ALL porque si las sumas son identicas solo obtendras una unica fila.

    En cuanto a tu pregunta, seria util saber que version de SQL Server usas. Si usas SS 2008 o mayor entonces has uso de los conjuntos de agrupacion (GROUPING SET) y/o nuevos operadores ROLLUP / CUBE, de lo contrario usa la forma antigua de rodar totales.

    SELECT SUM(c1) AS sum_c1, SUM(c2) AS sum_c2
    FROM (
    SELECT 1 AS grp, 10 AS c1, 20 AS c2
    UNION ALL
    SELECT 2 AS grp, 3 AS c1, 7 AS c2
    ) AS T
    GROUP BY
    	grp WITH ROLLUP 
    ORDER BY
    	GROUPING(grp),
    	grp;
    -- SS 2008 o mayor
    SELECT SUM(c1) AS sum_c1, SUM(c2) AS sum_c2
    FROM (
    SELECT 1 AS grp, 10 AS c1, 20 AS c2
    UNION ALL
    SELECT 2 AS grp, 3 AS c1, 7 AS c2
    ) AS T
    GROUP BY
    	GROUPING SETS ((grp), ())
    ORDER BY
    	GROUPING(grp),
    	grp;
    SELECT SUM(c1) AS sum_c1, SUM(c2) AS sum_c2
    FROM (
    SELECT 1 AS grp, 10 AS c1, 20 AS c2
    UNION ALL
    SELECT 2 AS grp, 3 AS c1, 7 AS c2
    ) AS T
    GROUP BY
    	ROLLUP(grp)
    ORDER BY
    	GROUPING(grp),
    	grp;
    GO


    AMB

    Some guidelines for posting questions...

    jueves, 6 de junio de 2013 15:24