duda con sum()

[jusal]

Que tal soy nuevo en el foro, tengo un problema con una consulta, tengo 2 tablas, Ventas y abonos, necesito hacer una consulta en la cual me salgan todas las ventas no pagadas y en una columna una sumatoria de sus abonos el problema viene que solo me muestra las ventas con abonos pero si no tienen ningún abono no aparece el registro utilizo la siguiente forma:

select folio,cliente,ventas.total,sum(abonos.total) from ventas,abonos where pagado='N' and abonos.referencia=ventas.folio group by folio

[quimfv]

Con tablas innodb

Código SQL:

SELECT folio,cliente,ventas.total,SUM(abonos.total) 
FROM ventas LEFT JOIN abonos 
ON abonos.referencia=ventas.folio
WHERE pagado='N'
GROUP BY folio
 

si usas myissam no estoy seguro si te funcionará esto

Código SQL:

SELECT folio,cliente,ventas.total,SUM(abonos.total) 
FROM ventas,abonos 
WHERE pagado='N' 
AND (abonos.referencia=ventas.folio OR abonos.referencia IS NULL) 
GROUP BY folio
 

Quim

[gnzsoloyo]

Nota Bene: El uso de INNER JOIN no requiere que sea una tabla InnoDB. El LEFT/RIGHT/INNER JOIN solamente hace una relación entre campos de dos tablas distintas, pero no requiere obligatoriamente que sean o no InnoDB, ni que los campos sean PK. Solamente requiere que los datos sean iguales para devolver resultados.

[quimfv]

Gracias por la precisión.

Luego la limitación esta solo en la definición de FK?

Quim

[gnzsoloyo]

Hay algunas diferencias más:
- InnoDB tiene transacciones y MyISAM no.
- InnoDB tiene un poderoso sistema de disaster recovery, que MyISAM no puede igualar.
- TRUNCATE tabla reinicia un auto_increment en InnoDB, pero en MyISAM hay que usar además ALTER TABLE.
- Las inserciones /actualizaciones son más rápidas en MyISAM que en InnoDB.
- InnoDB usa un sólo archivo por base para todas las tablas. MyISAM usa tres por cada una, pero InnoDB además no tiene límite de longitud de tablas porque simplemente crea otro archivo y lo enlaza. Por eso el límite de una base InnoDB está dado por el límite del sistema operativo y no del DBMS.
- Las tablas MyISAM soportan índices FULLTEXT, que las InnoDB no tienen, por lo que resultan óptimas para desarrollo de buscadores por expresiones naturales.

En general, el tema pasa por lo que le das prioridad:
- Seguridad de inserciones (ACID), consistencia e intergidad, consultas eficientes, baja incidencia INSERT/SELECT, alto nivel de concurrencia: InnoDB
- Rapidez de consultas, mucha incidencia de INSERT/UPDATE, bajo nivel de concurrencia, poco impacto de bloqueos: MyISAM.

[quimfv]

Gracias,

siempre he trabajado con innoDB, por lo que por alguna respuesta que habia leido por aquí me hice con la idea de que JOIN y sus modificadores eran exclusivos de InnoDB.

Quim

[jusal]

que tal funciona muy bien, pero no recordaba algo que ya que me di cuenta se lo agregue, por motivos de seguridad toda venta o abono solo si se da el caso se cancela no se elimina entonces en la BD aparece con status de 'C' entonces al agregarle esa condición me pasa lo mismo solo me muestra las ventas que tienen abonos.

SELECT folio,cliente,ventas.total,sum(abonos.total) FROM abonos LEFT JOIN ventas ON abonos.referencia=ventas.folio WHERE pagado='N' and ventas.status='A' and abonos.status='A' GROUP BY folio

[gnzsoloyo]

Primero: No uses palabras reservadas como "status" sin encerrarlas entre acentos inversos. te pueden casar problemas.
Segundo: En la consulta tal y como la estás escribiendo, le estás pidiendo que te devuelva unicamente los que tienen "A" en `status`. Es obvio que sólo te devovlerá esas.

Código MySQL:

SELECT 
    folio,
    cliente,
    ventas.total,
    sum(abonos.total) 
FROM 
    abonos LEFT JOIN ventas ON abonos.referencia=ventas.folio 
WHERE 
    pagado='N' AND 
    ventas.status='A' AND 
    abonos.status='A' --  <- con esta condición sólo cuentas los "A"
GROUP BY folio
 

[jusal]

ok quitare lo del status pero como resuelvo lo otro??
la consulta la puse alreves

SELECT folio,cliente,ventas.total,sum(abonos.total)
FROM
ventas LEFT JOIN abonos ON abonos.referencia=ventas.folio
WHERE
pagado='N' and ventas.status='A' and abonos.status='A' GROUP BY folio

si quito el abonos.status='A' solo me muestra ventas con abonos el problema que si lo quito me puede sumar de abonos cancelados por el sistema y pues seria malo eso así que lo que quiero es que en el sum solo sume los abonos Activos y no los cancelados

[gnzsoloyo]

Y... entonces descarta los cancelados:

Código MySQL:

SELECT 
    folio,
    cliente,
    ventas.total,
    sum(abonos.total) 
FROM 
    abonos LEFT JOIN ventas ON abonos.referencia=ventas.folio 
WHERE 
    pagado='N' AND 
    ventas.status='A' AND 
    abonos.status != 'C'
GROUP BY folio
 

[jusal]

sale igual y ya cambie lo de estatus por si acaso, me sigue mostrando solo las ventas con abonos :(

[gnzsoloyo]

Entonces qué condiciones tienes en `pagados` y en `abonos`. `status`, ya que tu consulta sólo pregunta por los abonos no pagados.
Si los hay en otras condiciones no aparecerán.

Analiza un poco más finamente la lógica que aplicas a las condiciones que filtra el WHERE.

[jusal]

la cosa es asi
Ventas
folio Cliente Total Pagado Status
0001 C001 $200 'N' A
0002 C002 $300 'S' A
0003 C003 $300 'N' A
0004 C004 $1000 'N' A
0005 C005 $500 'N' A

Abonos
Abono Rereferencia Total Status
A0001 0001 $100 A
A0002 0002 $200 C
......
A0015 0002 $300 A

Resultado de la consulta
Folio Cliente Total Abonos Resto
0001 C001 $200 $100 $100
0003 C003 $300 $0 $300
0004 C004 $1000 $0 $1000
0005 C005 $500 $0 $500


a como esta la consulta me mostraría 500 en lo abonado porque suma los 2 abonos tanto el Cancelado como el que si se aplico y si le modifico lo del status del abono solo me mostraría el primer registros de la consulta

[jusal]

ya lo solucione le agregue esto (select * from abonos where status='A') en lugar de poner Abonos gracias me fueron de mucha ayuda :)

SELECT
folio,
cliente,
ventas.total,
sum(abs.total)
FROM
ventas LEFT JOIN (select * from abonos where status='A') abs ON abs.referencia=ventas.folio
WHERE
pagado='N' AND
ventas.status='A'
GROUP BY folio

[gnzsoloyo]

Cuidado con ABS, es una palabra reservada. Es la función ABS(), que devuelve el valor absoluto (sin signo) de un número.

Si la vas a usar ponela entre acentos inversos.

[jusal]

ok, eso no lo sabia muchas gracias por su ayuda