Posts: 1956

Joined: Fri Oct 07, 2005 07:08 PM

ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 10:10 AM

Hola.

necesito tener el total de "c11", con esta consulta logro sumar por registro el total de los GRATIS + PAGOS + CERTIFICADOS
ahora al final quiero el total "general" de eoss pagos + gratis + certificados. Sin tener un recorrido por la consulta obtenida e ir
sumando esos "c11"... como puedo hacerlo por medio de del SELECT?

Code (fw): Select all Collapse

#define BRW_DIARIO_1 ;
"select " + ;
"a.pago        as c5, " + ;
"a.pobreza     as c6, " + ;
"a.gratis      as c7, " + ;
"(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a " + ;
"ORDER BY a.iditem "

gracias

FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/

FranciscoA

Posts: 2170

Joined: Fri Jul 18, 2008 01:24 AM

Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 08:32 PM

Aqui un ejemplo por el que podés guiarte.

Code (fw): Select all Collapse

  SELECT FACTU, SUM(GRAVABLE) AS GRAVABLE, SUM(IVA) AS IMPTO, SUM(GRAVABLE + IVA) AS TOTFACT
  FROM FACTURAH 
  GROUP BY FACTU WITH ROLLUP  ;

Francisco J. Alegría P.

Chinandega, Nicaragua.

Fwxh-MySql-TMySql

cmsoft

Posts: 1344

Joined: Wed Nov 16, 2005 09:14 PM

Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 08:45 PM

Si lo quieres en una misma consulta

Code (fw): Select all Collapse

#define BRW_DIARIO_1 ;
"(select " + ;
"a.pago        as c5, " + ;
"a.pobreza     as c6, " + ;
"a.gratis      as c7, " + ;
"(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a " + ;
"ORDER BY a.iditem) "+; 
"union all "+;
"(select sum(a.pago) as c5,sum(a.pobreza) as c6, sum(a.gratis) as c7, sum(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a )"
 "

Sino lo podés calcular al momento de ponerlo en el xbrowse

Salu2
Cesar Gomez
https://www.bcnsoft.com.ar
https://www.pedidosbcn.com

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 09:29 PM

Local cSql

Try your Sql Statement like this .. Much easier to read ...

define BRW_DIARIO_1

cSql := "select a.pago as c5, "
cSql += "a.pobreza as c6, "
cSql += "a.gratis as c7, "
cSql += "(a.pago + a.pobreza + a.gratis) as c11 "
cSql += "from tbdiario a "
cSql += "ORDER BY a.iditem "

Rick Lipkin

goosfancito

Posts: 1956

Joined: Fri Oct 07, 2005 07:08 PM

Re: ayuda con consulta y suma de columnas
Posted: Thu Dec 09, 2021 08:53 PM

de a poco quiero entender el mecanismo.

tengo la consulta general (select * from...") y necesito obtener el TOTAL "c7", los datos de la consulta
son estos:
1 1/1/20 2 3
2 2/1/20 2 0
2 3/1/20 0 1
1 1/1/20 0 1
1 2/1/20 0 0
2 3/1/20 1 1
5 4/1/20 2 2 (este esta en la consulta pero no interesa para el final)

lo que hice fue aplicar un filtro:

Code (fw): Select all Collapse

      ::oQry:setfilter( "c4=1 or c4=2" )

lo ordene por fecha y tambien por "c4"

Code (fw): Select all Collapse

      ::oQry:setorder("c4, c5")

pero no se ahora como hacer la suma. lo que necesito como salida es esto:

c4 c5 c6 c7
1 --- 1/1/20 --- 2 --- 3
1 --- 2/1/20 --- 2 --- 0
1 --- 3/1/20 --- 0 --- 1
(4 que es la suma de C7)

2 --- 1/1/20 --- 0 --- 1
2 --- 2/1/20 --- 0 --- 0
2 --- 3/1/20 --- 1 --- 1
(2)

FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/

cmsoft

Posts: 1344

Joined: Wed Nov 16, 2005 09:14 PM

Re: ayuda con consulta y suma de columnas
Posted: Thu Dec 09, 2021 10:03 PM

En ese caso, si quieres por cada tipo distinto de C4, su detalle y luego su total, la consulta sería.

Code (fw): Select all Collapse

"select * FROM (select a.c4," + ;
"a.pago        as c5, " + ;
"a.pobreza     as c6, " + ;
"a.gratis      as c7, " + ;
"(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a " + ;
"ORDER BY a.iditem) "+;
"union all "+;
"(select a.c4, sum(a.pago) as c5,sum(a.pobreza) as c6, sum(a.gratis) as c7, sum(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a GROUP BY a.c4 ) res ORDER BY res.c4"

Salu2
Cesar Gomez
https://www.bcnsoft.com.ar
https://www.pedidosbcn.com

goosfancito

Posts: 1956

Joined: Fri Oct 07, 2005 07:08 PM

Re: ayuda con consulta y suma de columnas
Posted: Fri Dec 10, 2021 01:40 AM

cmsoft wrote:En ese caso, si quieres por cada tipo distinto de C4, su detalle y luego su total, la consulta sería.

Code (fw): Select all Collapse

"select * FROM (select a.c4," + ;
"a.pago        as c5, " + ;
"a.pobreza     as c6, " + ;
"a.gratis      as c7, " + ;
"(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a " + ;
"ORDER BY a.iditem) "+;
"union all "+;
"(select a.c4, sum(a.pago) as c5,sum(a.pobreza) as c6, sum(a.gratis) as c7, sum(a.pago + a.pobreza + a.gratis) as c11 "+;
"from tbdiario a GROUP BY a.c4 ) res ORDER BY res.c4"

Gacias!

FWH 21.02
Harbour 3.2.0dev (r2104281802)
Copyright (c) 1999-2021, https://harbour.github.io/

FiveTech Support Forums

define BRW_DIARIO_1

Continue the discussion