Select con COUNT y SUM no me funciona

[Angel.Matilla]

Hace tiempo planteé un duda similar a la que tengo ahora y pensé que adaptando el código que distéis en su momento podría valerme, pero no he sido capaz.

Tengo este query sobre una vista de FB 2.5.6:

Código SQL [-]
SELECT COUNT(DISTINCT Codigo) Recibos, Periodo, Forma, Municipio
  FROM Rc03 
 WHERE PerPago = 2
   AND ForPago IN (2,4)
 GROUP BY PerPago, Periodo, ForPago, Forma, Junta, NomJunta
 ORDER BY PerPago, Forma, NomJunta

que me da este resultado:

Yo sé que el importe de esos dos registros es de 150€, pero si en el query añado el SUM así:

Código SQL [-]
SELECT COUNT(DISTINCT Codigo) Recibos, SUM(Cuota) Importe, Periodo, Forma, Municipio
  FROM Rc03 
 WHERE PerPago = 2
   AND ForPago IN (2,4)
 GROUP BY PerPago, Periodo, ForPago, Forma, Junta, NomJunta
 ORDER BY PerPago, Forma, NomJunta

lo que salees esto:

y no sé como hacer para que me dé ese resultado.
He intentado haciendo un subselect:

Código SQL [-]
SELECT COUNT(DISTINCT A.Codigo) Recibos, A.Periodo, A.Forma, A.NomJunta,
       (SELECT SUM(Cuota) FROM Cuotas WHERE Codigo = A.Codigo)
  FROM Rc03 A
 WHERE A.PerPago = 2
   AND A.ForPago IN (2,4)
 GROUP BY A.PerPago, A.Periodo, A.ForPago, A.Forma, A.Junta, A.NomJunta
 ORDER BY PerPago, Forma, NomJunta

ya que el importe que hay que sumar está en esa tabla CUOTAS y el campo código es el que relaciona ambas tablas, pero al tratar de ejecutar el query me da un error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

y no se me ocurre como solucionarlo.

También he probado esto otro:

Código SQL [-]
SELECT A.Periodo, A.Forma, A.NomJunta,
       (SELECT COUNT(Codigo) FROM Persona WHERE Codigo = A.Codigo),
       (SELECT SUM(Cuota) FROM Cuotas WHERE Codigo = A.Codigo)
  FROM Rc03 A
 WHERE A.PerPago = 2
   AND A.ForPago IN (2,4)
 GROUP BY A.PerPago, A.Periodo, A.ForPago, A.Forma, A.Junta, A.NomJunta
 ORDER BY PerPago, Forma, NomJunta

ya que el campo CODIGO es parte de la clave primaria de esa tabla PERSONA y por lo tanto no está repetido, pero me da el mismo error.

[[kuan-yiu]]

¿Y la primera te funciona en Firebird?
Yo tenía entendido que en la cláusula group by tienen que estar listados todos los campos del select con valores no agrupados (los que no tienen, sum, count...). Supongo que los pones porque están dentro del where.

En todo caso el error es referido a eso, que has añadido una columna y no la incluyes en el group by.

[cloayza]

Estimado Angel.Matilla

Creo que el problema de la suma de importe, puede deberse a los datos que te entrega la vista. Ya que la suma la realiza sobre ese dataset...

Podrías compartir el código SQL de la vista que utilizas...

Saludos cordiales

[Angel.Matilla]

Empezado por kuan-yiu

¿Y la primera te funciona en Firebird?
Yo tenía entendido que en la cláusula group by tienen que estar listados todos los campos del select con valores no agrupados (los que no tienen, sum, count...). Supongo que los pones porque están dentro del where.

En todo caso el error es referido a eso, que has añadido una columna y no la incluyes en el group by.

Lo del group by que comentas también me ha sorprendido a mi. Tanto en el programa BUilder como en SQLManager si lo pongo como está funciona y si quito los campos que no están en el SELECT me da error. No lo entiendo yo tampoco.

Empezado por cloayza

Estimado Angel.Matilla

Creo que el problema de la suma de importe, puede deberse a los datos que te entrega la vista. Ya que la suma la realiza sobre ese dataset...

Podrías compartir el código SQL de la vista que utilizas...

Saludos cordiales

Pero no quiero protestas porque sea muy complejo

Código SQL [-]
CREATE VIEW Rc03 (CodPrv, Codigo, Junta, ForPago, PerPago, Cuota, F_alta, Fecha, Situacion, Forma, Periodo, NomJunta) AS
SELECT A.CodPrv, A.Codigo, A. Junta, A.ForPago, A.PerPago, B.Cuota, A.F_alta, C.Fecha, D.Literal, E.Literal, F.Literal, G.Nombre 
  FROM Persona A 
  LEFT JOIN Cuotas B ON A.CodPrv = B.CodPrv AND A.Codigo = B.Codigo AND B.Tipo = 'A' 
  LEFT JOIN Recibos C ON A.CodPrv = C.CodPrv AND A.Codigo = C.Codigo 
  LEFT JOIN Instalacion D ON D.Etiqueta = 'Situacion' AND A.Situacion = D.Valor
  LEFT JOIN Instalacion E ON E.Etiqueta = 'ForPago' AND A.ForPago = E.Valor
  LEFT JOIN Instalacion F ON F.Etiqueta = 'PerPago' AND A.PerPago = F.Valor, Junta G 
 WHERE B.Cuota > 0 
   AND A.Situacion IN (SELECT Valor FROM Instalacion WHERE Etiqueta = 'Situacion' AND Situacion = 1) 
   AND A.ForPago IN (SELECT Valor FROM Instalacion WHERE Etiqueta = 'ForPago') 
   AND A.PerPago IN (SELECT Valor FROM Instalacion WHERE Etiqueta = 'PerPago') 
   AND A.Codigo NOT IN (SELECT Codigo FROM Emision) 
   AND A.CodPrv = G.CodPrv AND A.Junta = G.Codigo

Es el código que crea la vista

[mamcx]

Pero y los datos originales?

[Angel.Matilla]

Esto sería una muestra del SELECT completo de la vista (en total salen como 22000 registros sin poner filtros). No sé si te refieres a esto.

[Angel.Matilla]

Para que os hagáis una idea:

• La tabla PERSONA tiene ahora 15670 registros
• La tabla CUOTAS tiene 4403 registros
• La tabla recibos tiene 36559 registros

Es evidente que es difícil poner una muestra de las tres tablas. La otra (INSTALACION) tiene los datos fijos de la instalación: formas y periodos de pago, etc, y prácticamente no se ha movido desde que se puso en marcha la aplicación hace ya como 10 ó 12 años.

[mamcx]

Obvio no queremos miles de registros, pero un conjunto pequeño que muestre los datos (en texto) permite validar el resultado.

[Angel.Matilla]

Al margen de que en cuanto pueda subiré una muestra de los registros de las tablas implicadas, hay una coas que me llama la atención en los dos últimos querys que puse. A ver si alguno me lo explica:

Código SQL [-]
SELECT COUNT(DISTINCT A.Codigo) Recibos, A.Periodo, A.Forma, A.NomJunta,
       (SELECT SUM(Cuota) FROM Cuotas WHERE Codigo = A.Codigo) Importe
  FROM Rc03 A
 WHERE A.PerPago = 2
   AND A.ForPago IN (2,4)
 GROUP BY A.PerPago, A.Periodo, A.ForPago, A.Forma, A.Junta, A.NomJunta
 ORDER BY PerPago, Forma, NomJunta

Si este query lo intento ejecutar tal y como está me da el error que antes citaba:

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Si en el group by incluyo ese subselect, como parece indicar el error, me genera el mismo error. ¿Cómo es posible? Es más: si os fijáis en el group by hay más columas que las que hay en el select y sin embargo no da error por esa razón.

[Angel.Matilla]

Empezado por mamcx

Obvio no queremos miles de registros, pero un conjunto pequeño que muestre los datos (en texto) permite validar el resultado.

Os adjunto algunos datos de las tablas Persona, Cuotas e Instalacion. El campo NomJunta o Municipio de los querys puede suprimirse tranquilamente del select. La primera línea de cada uno de los ficheros indica los nombres de los campos.

[engranaje]

A ver si puedo ser de ayuda.
Entiendo que una consulta como esta:

Código SQL [-]
 (Select A.codigo, A.Periodo, A.Forma, A.NomJunta, SUM(Cuotas.Cuota) importeCuota
 from
 Rc03 A inner join Cuotas on A.codigo = Cuotas.codigo
 WHERE A.PerPago = 2
 AND A.ForPago IN (2,4)
 GROUP BY A.codigo,A.Periodo, A.Forma, A.NomJunta
 ORDER BY A.PerPago, A.Forma, A.NomJunta

Mostrará el sumatorio de los importes de cada uno de los códigos de cuota posible en cada tupla periodo,forma, nomjunta que cumple el filtro.

Entiendo que esto no es lo que quieres porque querrías cuantos códigos de cuota posibles distintos hay y el importe total de de todos esos código. Propongo esta consulta par probar:

Código SQL [-]
Select count(codigo) Recibos, Periodo, Forma, NomJunta, sum(importeCuota) importe
from
 (Select A.codigo, A.Periodo, A.Forma, A.NomJunta, SUM(Cuotas.Cuota) importeCuota
 from
 Rc03 A inner join Cuotas on A.codigo = Cuotas.codigo
 WHERE A.PerPago = 2
 AND A.ForPago IN (2,4)
 GROUP BY A.codigo,A.Periodo, A.Forma, A.NomJunta
 ORDER BY A.PerPago, A.Forma, A.NomJunta)
 group by Peridodo, Forma, NomJunta

si no he cometido ningún error al escribirlas estas consultas deberían ejecutarse sin problema. ¿Podrías probarlas y comentar si los resultados de las dos se ajustan a lo que esperas o si la primera ya muestra un importeCuota que no corresponde?

[Angel.Matilla]

Gracias por la sugerencia pero ambas dan el mismo error de antes, con el agravante que en la segunda, al haber dos GROUP BY, no es posible saber a cual de ellos afecta auqnue parece que se a la primera línea del query:

Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).

Además el campo CUOTA está incluido en la definición de la vista como se puede ver.

[engranaje]

Perdón, Sí parece que he cometido varios errores, esto escribiendo sin probar contra bd y sin tener las tablas y parece que he cometido unos cuantos errores de bulto.


Poniendo peridodo en lugar de periodo en el group by y entendiendo que el cuota que querías era el de cuotas y no la de Rc03. Las cambio un poco, a ver. Esta noche si no has resuelto aún probaré a montar una estructura en mi firebird probar que está claro que escribir así sin probar no es lo mio.

Código SQL [-]
 
 Select codigo, Periodo, Forma, NomJunta, SUM(Cuota) importeCuota 
 from  Rc03   
 WHERE PerPago = 2  
 AND ForPago IN (2,4) 
 GROUP BY codigo,Periodo, Forma, NomJunta  
ORDER BY PerPago, Forma, NomJunta

Código SQL [-]
Select count(codigo) Recibos, Periodo, Forma, NomJunta, sum(importeCuota) importe from  
( Select codigo, Periodo, Forma, NomJunta, SUM(Cuota) importeCuota  
 from  Rc03   
 WHERE PerPago = 2  
 AND ForPago IN (2,4)  
 GROUP BY codigo,Periodo, Forma, NomJunta 
 ORDER BY PerPago, Forma, NomJunta)  
 group by Periodo, Forma, NomJunta

[mamcx]

Empezado por Angel.Matilla

Os adjunto algunos datos de las tablas Persona, Cuotas e Instalacion. El campo NomJunta o Municipio de los querys puede suprimirse tranquilamente del select. La primera línea de cada uno de los ficheros indica los nombres de los campos.

Trate de convertir eso a sql, pero la vista dice que faltan campos. No entiendo porque eso en CSV (quizás me entendiste literalmente que en "texto" cuando me pusiste la imagen? Lo que quise decir es que no pongas imágenes!).

[Angel.Matilla]

Empezado por engranaje

Perdón, Sí parece que he cometido varios errores, esto escribiendo sin probar contra bd y sin tener las tablas y parece que he cometido unos cuantos errores de bulto.

Ambos siguen dando el mismo error y no lo entiendo.

Empezado por mamcx

Trate de convertir eso a sql, pero la vista dice que faltan campos. No entiendo porque eso en CSV (quizás me entendiste literalmente que en "texto" cuando me pusiste la imagen? Lo que quise decir es que no pongas imágenes!).

Vale. Te entendí mal, lo siento. En los ficheros que subí sólo incluí los campos que llamo en el query y, sí, tienes razón y debí incluir todos los que usa la vista. Trataré de arreglarlo.

[engranaje]

Puedes probar sí esta consulta:

Código SQL [-]
Select Codigo, PerPago, Forma, Periodo, NomJunta, SUM(Cuota) importeCuota   
from  Rc03    
WHERE PerPago = 2   
AND ForPago IN (2,4)  
GROUP BY Codigo, PerPago, Forma, Periodo, NomJunta
ORDER BY PerPago, Forma, NomJunta

te da un error:

Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).

Como lo entiendo todos los campos en el select menos el SUM están en el group by, el problema indicado en el error es porque cometí otro error para variar y estaba ordenando por PerPago sin meterlo en el group by.
En esta sentencia todos los campos en el order by están en el group by también. ¿Puedes probar que esta sentencia se ejecuta sin problemas?

[Angel.Matilla]

Empezado por engranaje

En esta sentencia todos los campos en el order by están en el group by también. ¿Puedes probar que esta sentencia se ejecuta sin problemas?

Esta no da error pero los resultados no son los que busco. No obstante, que un campo esté o no en el order by no influye si debe estar y está en el group. Seguiré probando...

[engranaje]

Claro, lo que estoy intentando hacer es ir por partes, si la consulta anterior se ejecuta sin problemas, puedes probar esta:

Código SQL [-]
Select count(codigo) Recibos, Periodo, Forma, NomJunta, sum(importeCuota) importe 
from   
( Select Codigo, PerPago, Forma, Periodo, NomJunta, SUM(Cuota) importeCuota      
from  Rc03       
WHERE PerPago = 2      
AND ForPago IN (2,4)     
GROUP BY Codigo, PerPago, Forma, Periodo, NomJunta   
ORDER BY PerPago, Forma, NomJunta
)  
group by Periodo, Forma, NomJunta

[Angel.Matilla]

No, tampoco funciona. Algo parecido ya lo había probado y me sigue sacando la suma de todos los recibos emitidos (4750 € en vez de 150 €). Sé que estoy planteando mal el query pero no veo donde.