GROUP BY y campos con SUM( )

[Ultraton500]

Saludos compañeros,

Tengo en una consulta SQL un campo agregado en el que utilizo la función SUM( ) y para cumplir con la norma debo incluir en la cláusula GROUP BY todos los campos de la consulta.

Esto produce el error "Cannot GROUP by aggregate field." y si quito del GROUP BY el campo agregado me genera el error "SQL: GROUP BY clause is missing or invalid."

El problemas aquí es la función SUM( ) ya que si la quito puedo incluir en el GROUP BY el campo agregado.

No encuentro manera de solucionar este problema por lo que cualquier ayuda será bienvenida y agradecida.

Saludos,

Javier.

[Mauricio R. Molinero]

Javier,

Tenes que agrupar por todas las columnas de la consulta o sino cambiar el SET ENGINEBEHAVIOR a 70

Saludos!

Mauricio R. Molinero,

[Ultraton500]

Gracias Mauricio por tu pronta respuesta.

Estoy trabajando provisoriamente con .dbf pero en breve voy a migrar a Firebird por lo que no puedo valerme del SET ENGINEBEHAVIOR.

Por otro lado ya probé de agrupar por todas las columnas pero si incluyo la columna agregada me devuelve el error "Cannot GROUP by aggregate field." y si no la incluyo me devuelve el error  "SQL: GROUP BY clause is missing or invalid.".

Saludos,

Javier.

[Víctor Hugo Espínola Domínguez]

Hola Javier

Muestra el código que usas.

Saludos,

Víctor.

Lambaré - Paraguay.

[MALKASOFT ADPI: http://www.developervfp.blogspot.com/]

Hola,

Me imagino que estas tratando de sacar todo los datos en un solo select incluyendo la suma y los campos que quieras mostrar, para eso puede hacer de la siguiente manera,

Por ejemplo en una consulta puedes obtener la suma y lo unes con un inner join

Select campo1, campo2, sumcapo from Tbtutabla INNER JOIN (Select Sum(Campo) AS sumcampo From Tutabla Where o Group by) TbSuma ON Tutabla.Codigo= TbSuma.codigo

Puede ser una solución, pero fíjate bien en el group by y ademas es posible que el mismo campo este en mas de una tabla así que tienes que ponerlo en el grupo

Saludos; 

Ing. Russvell Jesus Soto Gamarra 

Canal YouTube: http://www.youtube.com/rycjesusrj

Blog: http://developervfp.blogspot.mx/

Framework Multi-conexion v6.0 trabaja cualquier base de datos

(SQLServer, MySQL, Firebird, MariaDB, PostgreSQL, Oracle y etc.) 

http://www.youtube.com/watch?v=PCCZvCaet74

  

[GeoSys Diseño de Software]

En este hilo se habló de eso: https://groups.google.com/forum/#!topic/publicesvfoxpro/W5Sh9g_I5bU

Puedes ver esta respuesta de un Ex del grupo:

Walter R. Ojeda Valiente

11/01/12

En el GROUP BY debes colocar TODAS LAS COLUMNAS que no sean calculadas (es decir, que no usen las funciones SUM(), AVG(), etc.)

Saludos.

Walter.

Saludos

Anthony Contreras Peralta

Costa Rica.

[Mario López]

@Javier / Ultraton:

en realidad no es cierto lo de que "para cumplir la norma tenés que incluir en el GROUP BY todos los campos de
la consulta", lo correcto es que tenés que incluir todos los campos NO CALCULADOS, como aparecía en el post
original de Walter que comentó Anthony:

>> En el GROUP BY debes colocar TODAS LAS COLUMNAS que no sean calculadas (es decir, que no usen las funciones SUM(), AVG(), etc.)

No pusiste tu estructura de datos ni tu consulta (algo que nos ayudaría a ayudarte), pero fijate de sacar del GROUP
BY las referencias a las columnas calculadas y debería funcionar.

HTH
Mario
---

El lunes, 2 de junio de 2014 17:36:57 UTC-3, Mauricio R. Molinero escribió:

[Ultraton500]

Hola Victor Hugo, gracias por responder.

La consulta original es demasiado extensa y confusa pero puedo simplificarla y reproducir el mismo problema con esta consulta:

SET ENGINEBEHAVIOR 90

SELECT nombre,saldo + SUM(monto) AS suma FROM clientes INNER JOIN cuentacorriente ON id_cliente=cliente GROUP BY apeynom

esto devuelve el error "SQL: GROUP BY clause is missing or invalid.", y si le añado la columna agregada en el GROUP BY:

SELECT nombre,saldo + SUM(monto) AS suma FROM clientes INNER JOIN cuentacorriente ON id_cliente=cliente GROUP BY apeynom,suma

devuelve el error "Cannot GROUP by aggregate field."

Por lo que veo, lo que está complicando las cosas es la suma ya que las consultas

SELECT nombre,saldo + monto AS suma FROM clientes INNER JOIN cuentacorriente ON id_cliente=cliente GROUP BY apeynom,suma

SELECT nombre,SUM(monto) AS suma FROM clientes INNER JOIN cuentacorriente ON id_cliente=cliente GROUP BY apeynom

no devuelven ningún error.

Quizá me convenga obtener los campos "saldo" y "SUM(monto) AS suma" por separado y hacer la suma  con SCAN... ENDSCAN registro a registro en un campo agregado.

Saludos cordiales,

Javier.

[Ultraton500]

Antony, Mario... gracias por sus aportes.

Estaba al tanto de que no hay que agrupar las columnas calculadas pero, como podrán ver en mi post de respuesta a Victor Hugo, la cosa tiene una vuelta mas de rosca que acabo de descubrir.

La complicación está en que la columna es una operación entre un campo y un campo calculado... Parece que eso no es compatible con GROUP BY.

Saludos cordiales,

Javier.

[Mario López]

@Javier:

como ya viste vos, la columna "mixta" te va a causar problemas, porque no es ni totalmente calculada ni totalmente fija para un grupo. La solución: convertirla en una columna totalmente calculada:

SELECT nombre,MIN(saldo) + SUM(monto) AS suma FROM clientes INNER JOIN cuentacorriente ON id_cliente=cliente GROUP BY apeynom

asumiendo que "Saldo" viene de la tabla de clientes.

Al usar MIN no cambiás el valor del saldo (que es el mismo para todo el grupo de apeynom). También podrías usar
MAX o AVG que para este caso sería lo mismo.

HTH
Mario
---

[Víctor Hugo Espínola Domínguez]

Hola Javier

Debe ser una consulta compuesta parecida a la siguiente:

SELECT cli.nombre, cli.saldo + ccc.SubTotal  AS suma ;
        FROM clientes cli ;
        INNER JOIN ( SELECT Cliente, SUM(monto) AS SubTotal FROM cuentacorriente ;
                                        GROUP BY Cliente ) ccc ;
               ON  cli.id_cliente = ccc.cliente ;
       GROUP BY cli.Nombre

Saludos,

Víctor.

Lambaré - Paraguay.

[Mario López]

@Javier:

para probar consultas complicadas, fijate un bloque de código que posteé con anterioridad, yo lo uso muy frecuentemente:

OPEN DATABASE G:\VFP\GESTION\DATOS\XXXX.EMP\Empresa CLOSE TABLES ALL SET MEMOWIDTH TO 90 * Acá hago las consultas para obtener el subconjunto de datos para la prueba SELECT Co.CoFecha, Co.CoTotal, Co.PeTipo, Co.PeCodigo ;     FROM Comprob Co WHERE CoFecha >= {6/12/2013} AND PeCodigo=="     5" ;     INTO CURSOR c_Comprob      SELECT DISTINCT Pe.PeTipo, Pe.PeCodigo, Pe.PeNombre FROM Persona Pe ;     JOIN c_Comprob Co ON Pe.PeTipo+Pe.PeCodigo==Co.PeTipo+Co.PeCodigo ;     INTO CURSOR c_Persona TEXT TO sSQL ****** Query a probar ******* SELECT * FROM c_Comprob Co JOIN c_Persona Pe ON Pe.PeTipo+Pe.PeCodigo==Co.PeTipo+Co.PeCodigo ENDTEXT  * Genero el bloque de texto con la generacion de los cursores y los datos de los mismos, * mas la instruccion SQL a ejecutar sobre dichos cursores sCode = ToAsciiTables() + sSQL * Cierro todao tablas para garantizar que se regeneren los cursores con toda la informacion que necesito CLOSE TABLES ALL  CLEAR ? sCode * _CLIPTEXT = sCode sTempFile = SYS(2023) + "\Repro.prg" STRTOFILE(sCode, sTempFile) MODIFY COMMAND (sTempFile) * EXECSCRIPT(sCode) * Acá grabo esto si lo voy a usar con posterioridad, o lo pego en el clipboard con _CLIPTEXT = sCode RETURN #define CRLF CHR(13)+CHR(10) #define F_NAME    1 #define F_TYPE    2 #define F_LEN    3 #define F_DEC    4 #define F_COL_WIDTH 5 * Vuelca todos los cursores abiertos en un prg FUNCTION ToAsciiTables(sAliasLst) LOCAL nSel, sCode, asUsed[1], sAlias sCode = "" FOR nSel = 1 TO AUSED(asUsed)     sAlias = asUsed[nSel, 1]     IF ! ISREADONLY(sAlias)        && Paso solo los cursores         LOOP      ENDIF          IF ! EMPTY(sAliasLst) AND ! LOWER(sAlias) $ LOWER(sAliasLst)         *      ENDIF     sCode = sCode + ToAsciiTable(sAlias) + CRLF NEXT RETURN sCode * Vuelca un alias en un prg FUNCTION ToAsciiTable(sAlias) LOCAL axFld[1], nFld, nFlds, sCode, nSel, nLen nSel = SELECT() SELECT (EVL(sAlias, SELECT())) sCode = [CREATE CURSOR ] + ALIAS() + [ (] nFlds = AFIELDS(axFld) FOR nFld = 1 TO nFlds     nLen = axFld[nFld, F_LEN]     sCode = sCode + IIF(nFld > 1, "," , "") + ;         axFld[nFld, F_NAME] + " " + ;         axFld[nFld, F_TYPE] + " (" + ;         TRANSFORM(nLen) + "," + ;         TRANSFORM(axFld[nFld, F_DEC]) + ")"     nLen = MAX(nLen, LEN(TRANSFORM(EVALUATE(axFld[F_NAME]))))     axFld[nFld, F_COL_WIDTH] = MAX(nLen, LEN(axFld[nFld, F_NAME])) NEXT sCode = sCode + [)] + REPLICATE(CRLF, 2) sCode = sCode + REPLICATE("*", 50) + CRLF sCode = sCode + "*" FOR nFld = 1 TO nFlds     sCode = sCode + PADR(axFld[nFld, F_NAME], axFld[nFld, F_COL_WIDTH]) + "|" NEXT sCode = sCode + CRLF sCode = sCode + [TEXT TO sStr NOSHOW] + CRLF SCAN     FOR nFld = 1 TO nFlds         sCode = sCode + PADR(TRANSFORM(EVALUATE(axFld[nFld, F_NAME])), axFld[nFld, F_COL_WIDTH]) + "|"     NEXT     sCode = sCode + CRLF ENDSCAN sCode = sCode + [ENDTEXT] + REPLICATE(CRLF, 2) sCode = sCode + [sFile = SYS(2023) + "/_append.txt"] + CRLF sCode = sCode + [STRTOFILE(sStr, sFile)] + CRLF sCode = sCode + [APPEND FROM (sFile) DELIMITED WITH CHARACTER "|"] + CRLF SELECT(nSel) RETURN sCode ***

Esto te genera este resultado (sobre mi conjunto de datos, obvio)

CREATE CURSOR C_PERSONA (PETIPO C (1,0),PECODIGO C (6,0),PENOMBRE C (30,0)) ************************************************** *PETIPO|PECODIGO|PENOMBRE                      | TEXT TO sStr NOSHOW B     |     5  |BANCO GALICIA                 | P     |     5  |LAVADERO MONUMENTAL           | ENDTEXT sFile = SYS(2023) + "/_append.txt" STRTOFILE(sStr, sFile) APPEND FROM (sFile) DELIMITED WITH CHARACTER "|" CREATE CURSOR C_COMPROB (COFECHA D (8,0),COTOTAL N (14,2),PETIPO C (1,0),PECODIGO C (6,0)) ************************************************** *COFECHA   |COTOTAL       |PETIPO    |PECODIGO  | TEXT TO sStr NOSHOW 06/12/2013|700           |B         |     5    | 12/12/2013|660           |B         |     5    | 12/12/2013|1000          |B         |     5    | 13/12/2013|820           |B         |     5    | 17/12/2013|700           |B         |     5    | 19/12/2013|700           |B         |     5    | 19/12/2013|400           |B         |     5    | 21/12/2013|4000          |B         |     5    | 23/12/2013|1100          |B         |     5    | 23/12/2013|700           |B         |     5    | 03/01/2014|1320          |B         |     5    | 31/12/2013|4932          |P         |     5    | 07/01/2014|820           |B         |     5    | 09/01/2014|820           |B         |     5    | 09/01/2014|820           |B         |     5    | 11/01/2014|1320          |B         |     5    | 11/01/2014|5940          |B         |     5    | 12/01/2014|820           |B         |     5    | 14/01/2014|600           |B         |     5    | 15/01/2014|660           |B         |     5    | 17/01/2014|2376          |B         |     5    | 20/01/2014|1500          |B         |     5    | ENDTEXT sFile = SYS(2023) + "/_append.txt" STRTOFILE(sStr, sFile) APPEND FROM (sFile) DELIMITED WITH CHARACTER "|" ****** Query a probar ******* SELECT * FROM c_Comprob Co JOIN c_Persona Pe ON Pe.PeTipo+Pe.PeCodigo==Co.PeTipo+Co.PeCodigo

Esto te permite a vos ver claramente todas las tablas y los datos vinculados en la consulta, además podés pegar el bloque de resultado directamente en un PRG y probar de modificar el query final hasta que se ajuste a lo que necesitás.
También es muy fácil hacer un diff del bloque y ver qué datos se modifican ante cualquier cambio de la consulta. Y, como bono, si no lográs lo que querés con el query, podés pegar el bloque directamente como consulta en el foro y así cualquiera puede reproducir completamente tu estructura de datos sin tener que adivinar y ayudarte con tu problema.

HTH
Mario
---

El lunes, 2 de junio de 2014 21:38:44 UTC-3, Ultraton500 escribió:

[Ultraton500]

Gracias Mario y Victor, ambas soluciones funcionan perfectamente !!

Y gracias también al resto de los compañeros que me ayudaron.

Puedo seguir adelante gracias a la ayuda que me han dado.

Saludos cordiales,

Javier.

<span style="color:#CF6

...

[Jorge Kiernan]

estimado Victor, mirando tu ejemplo 

SELECT cli.nombre, cli.saldo + ccc.SubTotal  AS suma ;
        FROM clientes cli ;
        INNER JOIN ( SELECT Cliente, SUM(monto) AS SubTotal FROM cuentacorriente ;
                                        GROUP BY Cliente ) ccc ;
               ON  cli.id_cliente = ccc.cliente ;
       GROUP BY cli.Nombre

¿como dan los tiempos de respuesta al anidar las consultas?

esto contra tomar el campo saldo, calcular en otro la suma, y eventualmente sumarlos al publicar luego el resultado, ya sea en la grilla, el informe o lo que sea.

SELECT clientes.nombre,clientes.saldo, SUM(cuentacorriente.monto) AS suma ;

FROM clientes INNER JOIN cuentacorriente ON clientes.id_cliente=cuentacorriente.cliente ;

GROUP BY clientes.nombre

esta otra estrategia, ¿no sería más rápida? Gracias por tu tiempo

[Víctor Hugo Espínola Domínguez]

Hola Jorge

Adjunto programa y datos para comparar.

Saludos,

Víctor.

Lambaré - Paraguay.