Page 1 of 1
ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 10:10 am
by goosfancito
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: Select all | Expand
#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
Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 8:32 pm
by FranciscoA
Aqui un ejemplo por el que podés guiarte.
Code: Select all | Expand
SELECT FACTU, SUM(GRAVABLE) AS GRAVABLE, SUM(IVA) AS IMPTO, SUM(GRAVABLE + IVA) AS TOTFACT
FROM FACTURAH
GROUP BY FACTU WITH ROLLUP ;
Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 8:45 pm
by cmsoft
Si lo quieres en una misma consulta
Code: Select all | Expand
#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
Re: ayuda con consulta y suma de columnas
Posted: Wed Dec 08, 2021 9:29 pm
by Rick Lipkin
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
Re: ayuda con consulta y suma de columnas
Posted: Thu Dec 09, 2021 8:53 pm
by goosfancito
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:
lo ordene por fecha y tambien por "c4"
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)
Re: ayuda con consulta y suma de columnas
Posted: Thu Dec 09, 2021 10:03 pm
by cmsoft
En ese caso, si quieres por cada tipo distinto de C4, su detalle y luego su total, la consulta sería.
Code: Select all | Expand
"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"
Re: ayuda con consulta y suma de columnas
Posted: Fri Dec 10, 2021 1:40 am
by goosfancito
cmsoft wrote:En ese caso, si quieres por cada tipo distinto de C4, su detalle y luego su total, la consulta sería.
Code: Select all | Expand
"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!