Orden en Select

[betodaniel61]

Buenas Noches

Tengo un problema con una 'select' en al cual hay varios 'left join', para generar sumas, todos funcionan bien pero el compras no, las que tienen 1 compra da 40, sin compras da 0.
Funciona OK cuando saco el 'left join cuentacorriente', JUSTO SE DA QUE 40 ES LA CANTIDAD DE REGISTROS QUE TENGO CON MI 'IDUSUARIO' EN 'CUENTACORRIENTE', puede tener que ver??

Les paso el código, es un poco extenso:

select publicaciones.*,tipopublicaciones.destagen,tipopub licaciones.destasec,paises.nombre as nompai,provincias.nombre as nompro,localidades.nombre as nomloc,usuarios.usuario as codusu,usuarios.nombre as nomusu,tipomonedas.simbolo as moneda,tipoarticulos.nombre as nomtar,publicaciones.cantidad-sum(if(compras.cantidad is not null,compras.cantidad,0)) as quedan,sum(if(compras.cantidad is not null,compras.cantidad,0)) as vendido,sum(if(tipomovimientos.signo='+',importe,i f(tipomovimientos.signo='-',importe*-1,0))) as saldo from publicaciones left join tipopublicaciones on tipopublicaciones.idtipopublicacion=publicaciones. idtipopublicacion left join usuarios on usuarios.idusuario=publicaciones.idusuario left join tipomonedas on tipomonedas.idtipomoneda=publicaciones.idtipomoned a left join tipoarticulos on tipoarticulos.idtipoarticulo=publicaciones.idtipoa rticulo left join paises on paises.idpais=publicaciones.idpais left join provincias on provincias.idprovincia=publicaciones.idprovincia left join localidades on localidades.idlocalidad=publicaciones.idlocalidad left join compras on compras.idpublicacion=publicaciones.idpublicacion left join cuentacorriente on cuentacorriente.idusuario=publicaciones.idusuario left join tipomovimientos on tipomovimientos.idtipomovimiento=cuentacorriente.i dtipomovimiento group by 1;

Muy Agradecido si pueden ayudarme.

[gnzsoloyo]

Mira, lo primero que tienes que tratar de hacer es ordenar la consulta, porque así como la escribes es inentendible.
Primero estructuremos la cosa:

Código MySQL:

select 
    publicaciones.*,
    tipopublicaciones.destagen,
    tipopublicaciones.destasec,
    paises.nombre as nompai,
    provincias.nombre as nompro,
    localidades.nombre as nomloc,
    usuarios.usuario as codusu,
    usuarios.nombre as nomusu,
    tipomonedas.simbolo as moneda,
    tipoarticulos.nombre as nomtar,
    publicaciones.cantidad-sum(if(compras.cantidad is not null,compras.cantidad,0)) as quedan,
    sum(if(compras.cantidad is not null,compras.cantidad,0)) as vendido,
    sum(if(tipomovimientos.signo='+', importe,if(tipomovimientos.signo='-',importe*-1,0))) as saldo 
from 
    publicaciones 
    left join tipopublicaciones on tipopublicaciones.idtipopublicacion=publicaciones.idtipopublicacion 
    left join usuarios on usuarios.idusuario = publicaciones.idusuario 
    left join tipomonedas on tipomonedas.idtipomoneda = publicaciones.idtipomoneda 
    left join tipoarticulos on tipoarticulos.idtipoarticulo=publicaciones.idtipoarticulo 
    left join paises on paises.idpais=publicaciones.idpais 
    left join provincias on provincias.idprovincia=publicaciones.idprovincia 
    left join localidades on localidades.idlocalidad=publicaciones.idlocalidad 
    left join compras on compras.idpublicacion=publicaciones.idpublicacion 
    left join cuentacorriente on cuentacorriente.idusuario=publicaciones.idusuario 
    left join tipomovimientos on tipomovimientos.idtipomovimiento=cuentacorriente.idtipomovimiento 
group by 1;
 

Aquí lo que se ve es que no has aprendido a usar alias para tablas, por lo que resulta algo difícil seguir la lógica aplicada.
Depuremos:

Código MySQL:

SELECT 
    P.*,
    TP.destagen,
    TP.destasec,
    PA.nombre nompai,
    PR.nombre nompro,
    LC.nombre nomloc,
    US.usuario codusu,
    US.nombre nomusu,
    TM.simbolo moneda,
    TA.nombre  nomtar,
    p.cantidad-sum(IF(CO.cantidad IS NOT NULL,CO.cantidad,0)) quedan,
    SUM(IF(CO.cantidad IS NOT NULL,CO.cantidad,0)) vendido,
    SUM(IF(TM.signo='+', importe, IF(TM.signo = '-',importe* - 1, 0))) saldo 
FROM 
    publicaciones P
    LEFT JOIN tipopublicaciones TP  ON P.idtipopublicacion  =   TP.idtipopublicacion 
    LEFT JOIN usuarios US           ON P.idusuario          =   US.idusuario 
    LEFT JOIN tipomonedas TM        ON P.idtipomoneda       =   TM.idtipomoneda 
    LEFT JOIN tipoarticulos TA      ON P.idtipoarticulo     =   TA.idtipoarticulo 
    LEFT JOIN paises PA             ON P.idpais             =   PA.idpais 
    LEFT JOIN provincias PR         ON P.idprovincia        =   PR.idprovincia 
    LEFT JOIN localidades LC        ON P.idlocalidad        =   LC.idlocalidad 
    LEFT JOIN compras CO            ON P.idpublicacion      =   CO.idpublicacion 
    LEFT JOIN cuentacorriente CC    ON P.idusuario          =   CC.idusuario 
    LEFT JOIN tipomovimientos TM    ON CC.idtipomovimiento  =   TM.idtipomovimiento 
GROUP BY 1;
 

Ahora podemos mirar un poco mejor lo que hay.

Lo primero que se puede observar es que estás definiendo todas las relaciones como opcionales, es decir, lo único seguro es que existe una publicación, que puede tener un tipo, puede corresponder a un usuario, puede tener un precio expresado en un tipo de moneda, puede tener un tipo de articulo, etc.
¿Realmente son todas opcionales? ¿No hay ninguna mandatoria?
Pregunto porque las mandatorias (obligatorias) se plantean con INNER JOIN, y no con LEFT JOIN. Eso simplifica mucho la consulta.
Por otro lado, en tu esquema original estás poniendo mal el ON de las tablas. Cuando usas el LEFT JOIN debes poner primero la tabla base (la que no es nula jamás) y luego la dependiente. En este caso el orden de los factores si altera el producto.
Además de eso, estás agrupando por un sólo campo, cuando en realidad deberías agrupar por todos los que no están afectados por la función (SUM()).
Si bien MySQL admite que se agrume por menos campos que los no afectados por funciones, eso si lo haces el resultado puede ser errático, ya que en los otros campos sólo dejará el primer valor que encuentre en el primer registro, escondiendo cualquier otro tipo de agrupamiento.

Finalmente, las sumas como las estás planteando, no están bien hechas. al menos en un caso estás sumando un valor simple contra el de un agrupamiento, con lo que la operación aritmética puede estar generando (y eso creo que es lo que te pasa) un producto cartesiano. Es decir: Está aplicando la suma a cada uno de los registros que suma, y no al total.
En esencia, lo que te conviene hacer es que el cálculo de suma se haga en una subconsulta que devuelva una tabla virtual en el FROM, y luego con esa tabla derivada haces el resto de los JOIN.
¿Se entiende la idea?

[betodaniel61]

Sii!!, me parece que estoy entendiendo lo que me escribiste, voy a empezar a ponerlo en práctica y te voy comentando como voy, Muchas Gracias por esta gran desinteresada explicación, me es muy util.

[betodaniel61]

GNZSOLOYO Buenas Tardes,
Amigo estoy aprolijándolo y llegue a esto por ahora:

SELECT
P.*,
TP.destagen,
TP.destasec,
PA.nombre AS nompai,
PR.nombre nompro,
LC.nombre nomloc,
US.usuario codusu,
US.nombre nomusu,
TM.simbolo moneda,
TA.nombre nomtar,
p.cantidad-sum(IF(CO.cantidad IS NOT NULL,CO.cantidad,0)) quedan,
SUM(IF(CO.cantidad IS NOT NULL,CO.cantidad,0)) vendido,
SUM(IF(TV.signo='+', importe, IF(TV.signo = '-',importe* - 1, 0))) saldo
FROM
publicaciones P
INNER JOIN tipopublicaciones TP ON P.idtipopublicacion = TP.idtipopublicacion
INNER JOIN usuarios US ON P.idusuario = US.idusuario
INNER JOIN tipomonedas TM ON P.idtipomoneda = TM.idtipomoneda
INNER JOIN tipoarticulos TA ON P.idtipoarticulo = TA.idtipoarticulo
INNER JOIN paises PA ON P.idpais = PA.idpais
INNER JOIN provincias PR ON P.idprovincia = PR.idprovincia
INNER JOIN localidades LC ON P.idlocalidad = LC.idlocalidad
LEFT JOIN compras CO ON P.idpublicacion = CO.idpublicacion
LEFT JOIN cuentacorriente CC ON P.idusuario = CC.idusuario
INNER JOIN tipomovimientos TV ON CC.idtipomovimiento = TV.idtipomovimiento
GROUP BY 1,2,3,4,5,6,7,8,9,10,11;

No entiendo bien el tema de las subconsulta para las sumas, sería casi lo mismo ya que suma dependiendo de la cantidad vendida para esa publicación o del signo de tipo del movimiento.
El problema esta en el VENDIDO que arrastra al QUEDAN.

Donde pondría el SELECT SUM(IF(CO.cantidad IS NOT NULL,CO.cantidad,0)) vendido FROM compras WHERE idpublicacion=p.idpublicacion.

Si es posible te agradecería me guies un poco mas.
Muchas Gracias

[fahs82]

para obtener las sumas el join lo pones seguido de una subconsulta para obtener la suma seria algo asi inner join (consulta select sum()) as totalardex

un ejemplo sencillo seria un select asi

select t1.vendedor,t2.devoluciones from vendedores as t1 inner join(select sum(cantidad)) as devoluciones from tabla_devoluciones group by vendedor as t2

espero que con este ejemplo sencillo te de una idea y puedas aplicarlo a tu consulta suerte

[betodaniel61]

La verdad no se porque no anda, ahora quedo asi:

select pu.idpublicacion,
(select sum(ifnull(cantidad,0)) from compras co where pu.idpublicacion=co.idpublicacion)
from publicaciones as pu

Cuando no tiene registros en compras suma null, teoricamente debería sumar 0

Gracias si pueden ayudarme

[gnzsoloyo]

El IFNULL() debe encerrar al SUM(), no al revés.

[betodaniel61]

Muchas Gracias, funciono perfecto!!