Consulta de resumen de dos tablas

[francap]

Hola, he intentado hacer esto de varias formas y a la conclusión que he llegado es que no tengo la más mínima idea de como se hace

Tengo estas tres tablas

ArtCompras

Fecha
IdArt
Cantidad (cantidades compradas en este registro)

ArtVenta

Fecha
IdArt
Cantidad (cantidades vendidas en este registro)

Articulo
IdArt
Modelo
----------------------------------------
Me gustaria obtener una consulta de resumen con
modelo | total compradas | total vendidas | Stock Actual
------------------------------------------
Lo primero que intente fue:

Código SQL:

SELECT  Articulos.IdArt, Articulos.Modelo, SUM(ArtCompras.Cantidad) AS Compradas,  SUM(ArtVenta.Cantidad) AS Vendidas, Compradas - Vendidas  AS Stock
FROM Articulos, ArtCompras, ArtVenta
WHERE Articulos.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
AND Articulos.IdArt=ArtCompras.IdArt
AND Articulos.IdArt=ArtVenta.IdArt
AND ArtCompras.IdArt=ArtVenta.IdArt
GROUP BY  Articulos.IdArt, Articulos.Modelo 
ORDER BY Articulos.Modelo;
 

El problema que tengo es que la cantidad de registros por artículo en una y otra tabla son diferente y la suma que me da para cada artículos es Cantidad por la cantidad de registros de la tabla que tiene más registros de ese artículo.
Osea que vendidas o compradas que tiene mayor cantidad de registros en la tabla da bien pero la que tiene menor cantidad de registros en la tabla falsea los datos

Bueno muchas gracias por su tiempo

[francap]

Hola.... Supongo que no se puede hacer con sql lo que quiero?

Es mejor hacer las dos consultas por separado y resolverlo desde PHP?

[gnzsoloyo]

Código MySQL:

SELECT  
    A.IdArt, 
    A.Modelo, 
    SUM(AC.Cantidad) Compradas,  
    SUM(AV.Cantidad) Vendidas, 
    (SUM(AC.Cantidad) - SUM(AV.Cantidad)) Stock
FROM 
    Articulos A
    LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
    LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
WHERE Articulos.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
GROUP BY  A.IdArt
ORDER BY A.Modelo;
 

Los errores:
1) No se puede usar el alias de una columna del SELECT dentro del mismo SELECT. Tampoco se puede usar dentro del WHERE.
2) JOINs implicitos no sirven para hacer consultas con funciones de agregación con más de dos tablas.
3) El FROM estaba mal definido, y las relaciones del WHERE también. No estaban buscando os datos efectivamente indicados, ya que hacían depender (por ejemplo) las ventas de las compras, cosa que podía no ser verdadera (que compres stock de un artículo no implica que hayas vendido ese artículo en todo el período).
4) Agrupar por más de una columna es innecesario, si ambas columnas implican al mismo registro. Si cada modelo tiene un sólo ID y cada ID corresponde a un solo modelo, agrupar por ambos solo recarga el proceso pero no agrega nada.

[francap]

Hola, Gracias por tu respuesta... te vas a reir pero no sabía ni que existia inner join, left y right

estoy probando sobre access y en el punto 4 yo pensaba lo mismo, pero no solo en este caso me ha pasado que si no agrupo por todos los campos que quiero mostrar no me toma la consulta, seguramente sea algún otro error de mi inexperiencia.

estoy leyendo algunos manuales al respecto pero no veo donde está el error que no me deja pasar la consulta..

Código MySQL:

SELECT  
    A.IdArt, 
    A.Modelo, 
    SUM(AC.Cantidad)  as Compradas,  
    SUM(AV.Cantidad)  as Vendidas, 
    (SUM(AC.Cantidad) - SUM(AV.Cantidad)) as Stock
FROM 
    Articulos A
    LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
    LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
WHERE Articulos.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
GROUP BY  A.IdArt
ORDER BY A.Modelo;
 

el mensaje de error es:

Error de sintaxis (falta operador) en la expresión de consulta'A.IdArt = AC.IdArt
LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdAr'.

Puede ser un problema del vendito access?

[gnzsoloyo]

estoy probando sobre access y en el punto 4 yo pensaba lo mismo, pero no solo en este caso me ha pasado que si no agrupo por todos los campos que quiero mostrar no me toma la consulta, seguramente sea algún otro error de mi inexperiencia.

Si estás usando Access, ¿por qué estás posteando en el Foro de MySQL?
Si no estás usando Access, sino MySQL, no pruebes las consultas en Access, porque no son compatibles.

Access y MySQL ni siquiera se parecen. Access tiene recursos de SQL muy limitados y no sirve ni como referencia, porque no es capaz de manejar las consultas de SQL mas o menos elaboradas, y tiene restricciones incluso para usar ANSI-SQL, que sería la forma estandar del SQL que todo DBMS es capaz de interpretar. Pero como Access no es un DBMS, no puede.

No mezcles Access con verdaderas bases de datos.

[francap]

Estoy totalmente de acuerdo contigo... Tengo un servidor donde uso MySql.. este es mi
sitio:
Pero estaba probando esto en access.

Gracias por tu ayuda, realmente fue de muy buena calidad.

[francap]

Ya estoy instalando wamp en win7 para hacer las pruebas... espero que funcione

[gnzsoloyo]

Muy buena idea .

[francap]

Hola, no fue nada dificil migrar a mysql la base de datos. Hace mucho que estaba por hacer esto y no me animaba. Ahora queda conectar con vb a ella, pero no creo que vaya a tener problemas con eso.

ejecute la consulta sobre mysql:

Código SQL:

SELECT  
    A.IdArt, 
    A.Modelo, 
    SUM(AC.Cantidad) Compradas,  
    SUM(AV.Cantidad) Vendidas, 
    (SUM(AC.Cantidad) - SUM(AV.Cantidad)) Stock
FROM 
    Articulos A
    LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
    LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
WHERE Articulos.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
GROUP BY  A.IdArt
ORDER BY A.Modelo;
 

#1054 - Unknown column 'Articulos.IdArt' in 'IN/ALL/ANY subquery'

Realmente e intentado leer algún tutorial sobre composiciones, pero no logro entenderlo del todo. Tal vez tengas algún link a algún manual que este bueno!!

Gracias denuevo.

[gnzsoloyo]

Faltaba cambiar un nombre de tabla por su alias:

Código MySQL:

SELECT 
    A.IdArt, 
    A.Modelo, 
    SUM(AC.Cantidad) Compradas, 
    SUM(AV.Cantidad) Vendidas, 
    (SUM(AC.Cantidad) - SUM(AV.Cantidad)) Stock
FROM 
    Articulos A
    LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
    LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
WHERE A.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
GROUP BY A.IdArt
ORDER BY A.Modelo;
 

De todos modos ese WHERE no estoy seguro de que eralmente se necesite:

Código MySQL:

SELECT 
    A.IdArt, 
    A.Modelo, 
    SUM(AC.Cantidad) Compradas, 
    SUM(AV.Cantidad) Vendidas, 
    (SUM(AC.Cantidad) - SUM(AV.Cantidad)) Stock
FROM 
    Articulos A
    LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
    LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
GROUP BY A.IdArt
ORDER BY A.Modelo;
 

[francap]

Hola, Gracias funcionó!!

La cláusula where, la uso para que no me muestre artículos que están ingresados en la Base de Datos. Pero de los cuales nunca compre ni uno. Están ingresados, porque cuando la gente me pide los voy registrando para ver si es necesario comprarlos en el futuro.

Cuando no hay registros de ventas muestra NULL y por tanto no puede sumar el stock... claro que solo tengo que mirar a la primera columna para ver cuantos compré. Pero hay forma de que muestre 0 cuando es NULL?

Gracias, más que satisfecho con tu respuesta y con lo que he aprendido por seguir tus consejos.

[francap]

Sabés que estoy revisando y me da el mismo error que cuando pregunté la primera vez. Te explico por ejemplo:

Deal articulo 1 hay un registro en la tabla compras de cantidad 5

en la tabla ventas hay 2 registros de venta de este artículo cada uno con cantidad 1 osea dos vendidas una por registro

el resultado de la consulta muestra:

compradas 10 vendidas 2 stock 8

debería mostrar

compradas 5 vendidas 2 stock 3

si la cantidad de registros de compradas y vendidas es igual para el artículo no hay problema muestra bien los datos, pero si hay más registros de vendidas que de compradas muestra mal los datos

[gnzsoloyo]

Cuando no hay registros de ventas muestra NULL y por tanto no puede sumar el stock... claro que solo tengo que mirar a la primera columna para ver cuantos compré. Pero hay forma de que muestre 0 cuando es NULL?

Para esos casos usas IFNULL:

Código MySQL:

SELECT 
* * A.IdArt, 
* * A.Modelo, 
* * IFNULL(SUM(AC.Cantidad), 0) Compradas, 
* * IFNULL(SUM(AV.Cantidad), 0) Vendidas, 
* * (IFNULL(SUM(AC.Cantidad),0) - IFNULL(SUM(AV.Cantidad),0)) Stock
FROM 
* * Articulos A
* * LEFT JOIN ArtCompras AC ON A.IdArt = AC.IdArt
* * LEFT JOIN ArtVenta AV ON A.IdArt = AV.IdArt
GROUP BY A.IdArt
ORDER BY A.Modelo;
 

[francap]

Se ve que estábamos publicando a la vez.

Tampoco sabía de la existencia del ifnull..... gracias

No se si leiste el mensaje que publicamos a la vez?

[francap]

Le hice las correcciones que tu me dijiste en el primer mensaje a la consulta que había hecho, y me da los mismos errores que está última:

Código MySQL:

SELECT  Articulos.IdArt, Articulos.Modelo, sum(ArtCompras.Cantidad) AS Compradas,  sum(ArtVenta.Cantidad) AS Vendidas, sum(ArtCompras.Cantidad) - sum(ArtVenta.Cantidad)  AS Stock
FROM Articulos, ArtCompras, ArtVenta
WHERE Articulos.IdArt IN (SELECT DISTINCT ArtCompras.IdArt FROM ArtCompras)
AND Articulos.IdArt=ArtCompras.IdArt
AND Articulos.IdArt=ArtVenta.IdArt
GROUP BY  Articulos.IdArt
ORDER BY Articulos.Modelo;
 

[gnzsoloyo]

Esa última no sirve para lo que quieres. Sólo una consulta usando LEFT JOIN puede acercarte al resultado deseado.
Esta tarde pruebo con datos tu problema. Ahora estoy en el trabajo y no tengo acceso a MyQSL.

[francap]

ok, muchas gracias por tu tiempo gnzsoloyo..... si quieres te paso un .sql con las tablas

[gnzsoloyo]

Iniciado por francap

ok, muchas gracias por tu tiempo gnzsoloyo..... si quieres te paso un .sql con las tablas

Eso sería una muy buena idea. De esa forma puedo probar con tu propio esquema.

[francap]

Ya hice un .sql Decime por privado como te lo envío

Saludos

[gnzsoloyo]

Lamentablemente la consulta es un poco compleja, porque para evitar la multiplicación de datos ambas cuentas (Compras y Ventas) deben estar consolidadas ya por separado, lo que significa que hay que hacer primero las subconsultas en el FROM y entonces hacer el INNER JOIN correspondiente.
La respuesta es en definitiva:

Código MySQL:

SELECT
  T1.IdArt, T1.Modelo, Compras, IFNULL(Ventas, 0) Ventas, (Compras - IFNULL(Ventas, 0) ) Saldo
FROM
  (SELECT
    A.IdArt,
    A.Modelo,
    SUM(AC.Cantidad) Compras
  FROM
    Articulos A
    INNER JOIN ArtCompras AC ON AC.IdArt = A.IdArt
  GROUP BY Ac.IdArt) T1
  LEFT JOIN
  (SELECT
    A.IdArt,
    SUM(AV.Cantidad) Ventas
  FROM
    Articulos A
    INNER JOIN ArtVenta AV ON A.IdArt = AV.IdArt
  GROUP BY A.IdArt) T2 ON T1.IdArt = T2.IdArt;
 

Respecto a las tablas, hay algunos cuantos defectos formales, pero los más importantes son:
- Usas campos DATETIME para Fecha y para Hora, cuando deberías usar DATE y TIME para cada uno. De ese modo evitas basura en los datos que puede, luego, generar errores no detectables.
- Estas usando FK sin declararlas, a pesar de que las tablas si son InnoDB.
- Hay campos que no usas. Si no los usas o no deben existir, o debes implementarlos en la programación.
- Hay identificadores adicionales en las tablas de compra y venta que no se entiened para qué se usan.

[francap]

Hola, es excelente el trabajo que haces en el foro!!

Todo funcionó correctamente, ahora solo me queda sentarme con algún manual y entenderlo. Gracias!!

Respecto de las correcciones, lo de usar un campo date y otro time no se me había ocurrido. La base estaba originalmente hecha en access, ya no me acuerdo si tiene la opción pero supongo que si. Voy a implementarlo de aquí en mas que ya me liberé de "Bill".
Las Fk estan delcaradas en access, no se porque no las exporto mysql migrator.

Los campos en realidad los uso todos, yo te envié las tablas que estaban directamente relacionadas con las consutlas.

Cada registro de la tabla artventas es un renglón digamos de la factura.... osea hay una factura que tiene x artventas y lo mismo pasa con artcompras.

y también hay una tabla exactamente igual a artvetnas y artcompras donde guardo los historiales de de modificaciones con el identificador del usuario que lo hizo fecha y hora (en elmismo registro datatime.. por ahora).

Bueno nada realmente queda chico cualquier cosa que pueda decir de la ayuda que me has dado y el tiempo que has perdido en ayudarme.

Gracias de nuevo, y estoy a las órdenes si en algún momento puedo ayudarte en algo.

Saludos

[gnzsoloyo]

Me alegro que te haya servido.

Como nota, te diría que habría que considerar hacerle una buena ingeniería reversa con MySQL Workbench a la base para revisarla y verificar si hay cosas que se pueden mejorar en el diseño.
Puede que la migración le haya sacado cosas o que se las agregara.
Piénsalo.


Saludos y suerte.