Compras y Ventas en la misma consulta

[alyciashape]

Tengo estas tablas.
articulo
claart (clave primaria)
nombre (varchar)

venta
id_venta (clave primaria)
claart (indice)
precio (float)
cantidad (int)
fecha (date)

empresa
claemp (clave primaria)
nomemp (varchar)

Y también la de compras que es exactamente igual a la de venta.

Tengo esta consulta para sacar las ventas y verlo por mes:

Código MySQL:

SELECT a.codigo, a.nombre, 
                ROUND(AVG(v.precio), 2) 'Media Ventas',
                SUM(IF(month(v.fecha)=1, v.cantidad, 0)) 'Enero',
                SUM(IF(month(v.fecha)=2, v.cantidad, 0)) 'Febrero',
                SUM(IF(month(v.fecha)=3, v.cantidad, 0)) 'Marzo',
                SUM(IF(month(v.fecha)=4, v.cantidad, 0)) 'Abril',
                SUM(IF(month(v.fecha)=5, v.cantidad, 0)) 'Mayo',
                SUM(IF(month(v.fecha)=6, v.cantidad, 0)) 'Junio',
                SUM(IF(month(v.fecha)=7, v.cantidad, 0)) 'Julio',
                SUM(IF(month(v.fecha)=8, v.cantidad, 0)) 'Agosto',
                SUM(IF(month(v.fecha)=9, v.cantidad, 0)) 'Septiembre',
                SUM(IF(month(v.fecha)=10, v.cantidad, 0)) 'Octubre',
                SUM(IF(month(v.fecha)=11, v.cantidad, 0)) 'Noviembre',
                SUM(IF(month(v.fecha)=12, v.cantidad, 0)) 'Diciembre',
                SUM(v.cantidad) 'Total Ventas'
                FROM articulo a INNER JOIN venta v ON a.claart = v.claart
                WHERE year(fecha)=2012
                GROUP BY codigo
 

Pero además quería añadir las compras en la misma consulta si es que hay alguna forma y no lo consigo. O la base de datos se queda congelada un rato hasta que me dice que que el SQL "Has gone away" o me da error.

Las compras son lo mismo cambiando la tabla venta por compra. Exactamente igual.

Y para rizar más el rizo ademas estas compras y ventas tienen una "claemp" (clave empresa) y aunque serían muchísimas columnas para comparar datos sería mucho más cómodo así que tendría que quedar algo así como:

claart | Media precio Compras | Compras Enero Empresa A | Compras Enero Empresa B

¿Es esto viable o es pedir demasiado a la bd?

[quimfv]

Código MySQL:

SELECT sbc.codigo, sbc.nombre, 
                ROUND(AVG(sbc.precioVenta), 2) 'Media Ventas',
                ROUND(AVG(sbc.precioCompra), 2) 'Media Compras',
                SUM(IF(MONTH(v.fecha)=1, sbc.CantidadVentas, 0)) 'EneroV',
                SUM(IF(MONTH(v.fecha)=1, sbc.CantidadCompras, 0)) 'EneroC',
                SUM(IF(MONTH(v.fecha)=2, sbc.CantidadVentas, 0)) 'FebreroV',
                SUM(IF(MONTH(v.fecha)=2, sbc.CantidadCompras, 0)) 'FebreroC',
                SUM(IF(MONTH(v.fecha)=3, sbc.CantidadVentas, 0)) 'MarzoV',
                SUM(IF(MONTH(v.fecha)=3, sbc.CantidadCompras, 0)) 'MarzoC',
                SUM(IF(MONTH(v.fecha)=4, sbc.CantidadVentas, 0)) 'AbrilV',
                SUM(IF(MONTH(v.fecha)=4, sbc.CantidadCompras, 0)) 'AbrilC',
                SUM(IF(MONTH(v.fecha)=5, sbc.CantidadVentas, 0)) 'MayoV',
                SUM(IF(MONTH(v.fecha)=5, sbc.CantidadCompras, 0)) 'MayoC',
                SUM(IF(MONTH(v.fecha)=6, sbc.CantidadVentas, 0)) 'JunioV',
                SUM(IF(MONTH(v.fecha)=6, sbc.CantidadCompras, 0)) 'JunioC',
                SUM(IF(MONTH(v.fecha)=7, sbc.CantidadVentas, 0)) 'JulioV',
                SUM(IF(MONTH(v.fecha)=7, sbc.CantidadCompras, 0)) 'JulioC',
                SUM(IF(MONTH(v.fecha)=8, sbc.CantidadVentas, 0)) 'AgostoV',
                SUM(IF(MONTH(v.fecha)=8, sbc.CantidadCompras, 0)) 'AgostoC',
                SUM(IF(MONTH(v.fecha)=9, sbc.CantidadVentas, 0)) 'SeptiembreV',
                SUM(IF(MONTH(v.fecha)=9, sbc.CantidadCompras, 0)) 'SeptiembreC',
                SUM(IF(MONTH(v.fecha)=10, sbc.CantidadVentas, 0)) 'OctubreV',
                SUM(IF(MONTH(v.fecha)=10, sbc.CantidadCompras, 0)) 'OctubreC',
                SUM(IF(MONTH(v.fecha)=11, sbc.CantidadVentas, 0)) 'NoviembreV',
                SUM(IF(MONTH(v.fecha)=11, sbc.CantidadCompras, 0)) 'NoviembreC',
                SUM(IF(MONTH(v.fecha)=12, sbc.CantidadVentas, 0)) 'DiciembreV',
                SUM(IF(MONTH(v.fecha)=12, sbc.CantidadCompras, 0)) 'DiciembreC',
                SUM(sbc.CantidadVentas) 'Total Compras'
                SUM(sbc.CantidadCompras) 'Total Compras'
FROM (
SELECT a1.codigo, a1.nombre, 
             c.precio precioCompra, 
             null as precioVenta,
             c.fecha,
             c.cantidad as "CatidadCompras", 
             0 as "CantidadVentas"
FROM articulo a1 INNER JOIN compras c ON a1.claart = c.claart
WHERE YEAR(c.fecha)=2012
UNION ALL
SELECT a2.codigo, a2.nombre,
             null as precioCompra, 
             v.precio as precioVenta, 
             v.fecha,
             0 as "CatidadCompras", 
             v.cantidad as "CantidadVentas"
FROM articulo a2 INNER JOIN ventas v ON a2.claart = v.claart
WHERE YEAR(v.fecha)=2012) as Sbc
GROUP BY sbc.codigo;
 

Algo así?

Mira si hace bien el precio medio.... ojo con el precio medio calculado así ....imagina esta situación



Op.......Precio.....Cantidad
Compra...1...........1
Compra...100.........100
Venta....100.........1
Venta....1...........100

Todo del mismo producto

Precio medio de venta (100+1)/2=50,5
Precio medio de compra (100+1)/2=50,5

Beneficio 0 (Cero)???????

Cuando en realidad estas perdindo vacas y cencerros.

Gastos (1x1)+(100x100)=10001
Ingresos (1x100)+(100x1)=200

Benficio 200-10001=-9801 (negativo)

[alyciashape]

Muchisimas gracias Quim. Sobretodo por la explicación de las medias. Voy a ver si me está dando bien.

Me daba algunos errores como los nombres de las tablas, ventas por venta y poco más, los arreglé, y ahora me dice.

No se encuentra v.fecha

Aquí con las pequeñas correcciones:

Código MySQL:

SELECT sbc.codigo, sbc.nombre, 
                ROUND(AVG(sbc.precioVenta), 2) 'Media Ventas',
                ROUND(AVG(sbc.precioCompra), 2) 'Media Compras',
                SUM(IF(MONTH(v.fecha)=1, sbc.CantidadVentas, 0)) 'EneroV',
                SUM(IF(MONTH(v.fecha)=1, sbc.CantidadCompras, 0)) 'EneroC',
                SUM(IF(MONTH(v.fecha)=2, sbc.CantidadVentas, 0)) 'FebreroV',
                SUM(IF(MONTH(v.fecha)=2, sbc.CantidadCompras, 0)) 'FebreroC',
                SUM(IF(MONTH(v.fecha)=3, sbc.CantidadVentas, 0)) 'MarzoV',
                SUM(IF(MONTH(v.fecha)=3, sbc.CantidadCompras, 0)) 'MarzoC',
                SUM(IF(MONTH(v.fecha)=4, sbc.CantidadVentas, 0)) 'AbrilV',
                SUM(IF(MONTH(v.fecha)=4, sbc.CantidadCompras, 0)) 'AbrilC',
                SUM(IF(MONTH(v.fecha)=5, sbc.CantidadVentas, 0)) 'MayoV',
                SUM(IF(MONTH(v.fecha)=5, sbc.CantidadCompras, 0)) 'MayoC',
                SUM(IF(MONTH(v.fecha)=6, sbc.CantidadVentas, 0)) 'JunioV',
                SUM(IF(MONTH(v.fecha)=6, sbc.CantidadCompras, 0)) 'JunioC',
                SUM(IF(MONTH(v.fecha)=7, sbc.CantidadVentas, 0)) 'JulioV',
                SUM(IF(MONTH(v.fecha)=7, sbc.CantidadCompras, 0)) 'JulioC',
                SUM(IF(MONTH(v.fecha)=8, sbc.CantidadVentas, 0)) 'AgostoV',
                SUM(IF(MONTH(v.fecha)=8, sbc.CantidadCompras, 0)) 'AgostoC',
                SUM(IF(MONTH(v.fecha)=9, sbc.CantidadVentas, 0)) 'SeptiembreV',
                SUM(IF(MONTH(v.fecha)=9, sbc.CantidadCompras, 0)) 'SeptiembreC',
                SUM(IF(MONTH(v.fecha)=10, sbc.CantidadVentas, 0)) 'OctubreV',
                SUM(IF(MONTH(v.fecha)=10, sbc.CantidadCompras, 0)) 'OctubreC',
                SUM(IF(MONTH(v.fecha)=11, sbc.CantidadVentas, 0)) 'NoviembreV',
                SUM(IF(MONTH(v.fecha)=11, sbc.CantidadCompras, 0)) 'NoviembreC',
                SUM(IF(MONTH(v.fecha)=12, sbc.CantidadVentas, 0)) 'DiciembreV',
                SUM(IF(MONTH(v.fecha)=12, sbc.CantidadCompras, 0)) 'DiciembreC',
                SUM(sbc.CantidadVentas) 'Total Ventas',
                SUM(sbc.CantidadCompras) 'Total Compras'
FROM (
SELECT a1.codigo, a1.nombre, 
             c.precio precioCompra, 
             NULL as precioVenta,
             c.fecha,
             c.cantidad as "CantidadCompras", 
             0 as "CantidadVentas"
FROM articulo a1 INNER JOIN compra c ON a1.claart = c.claart
WHERE YEAR(c.fecha)=2012
UNION ALL
SELECT a2.codigo, a2.nombre,
             NULL as precioCompra, 
             v.precio as precioVenta, 
             v.fecha,
             0 as "CantidadCompras", 
             v.cantidad as "CantidadVentas"
FROM articulo a2 INNER JOIN venta v ON a2.claart = v.claart
WHERE YEAR(v.fecha)=2012) as Sbc
GROUP BY sbc.codigo
 

No veo el problema la verdad porque la consulta me parece complejísima y no entiendo ya lo de FROM (consultas varias)

Efectivamente así me da mal la media tal y como tú decías ya que la cantidad no entra en juego.

[quimfv]

Código MySQL:

SELECT sbc.codigo, sbc.nombre, 
                ROUND(AVG(sbc.precioVenta), 2) 'Media Ventas',
                ROUND(AVG(sbc.precioCompra), 2) 'Media Compras',
                SUM(IF(MONTH(sbc.fecha)=1, sbc.CantidadVentas, 0)) 'EneroV',
                SUM(IF(MONTH(sbc.fecha)=1, sbc.CantidadCompras, 0)) 'EneroC',
                SUM(IF(MONTH(sbc.fecha)=2, sbc.CantidadVentas, 0)) 'FebreroV',
                SUM(IF(MONTH(sbc.fecha)=2, sbc.CantidadCompras, 0)) 'FebreroC',
                SUM(IF(MONTH(sbc.fecha)=3, sbc.CantidadVentas, 0)) 'MarzoV',
                SUM(IF(MONTH(sbc.fecha)=3, sbc.CantidadCompras, 0)) 'MarzoC',
                SUM(IF(MONTH(sbc.fecha)=4, sbc.CantidadVentas, 0)) 'AbrilV',
                SUM(IF(MONTH(sbc.fecha)=4, sbc.CantidadCompras, 0)) 'AbrilC',
                SUM(IF(MONTH(sbc.fecha)=5, sbc.CantidadVentas, 0)) 'MayoV',
                SUM(IF(MONTH(sbc.fecha)=5, sbc.CantidadCompras, 0)) 'MayoC',
                SUM(IF(MONTH(sbc.fecha)=6, sbc.CantidadVentas, 0)) 'JunioV',
                SUM(IF(MONTH(sbc.fecha)=6, sbc.CantidadCompras, 0)) 'JunioC',
                SUM(IF(MONTH(sbc.fecha)=7, sbc.CantidadVentas, 0)) 'JulioV',
                SUM(IF(MONTH(sbc.fecha)=7, sbc.CantidadCompras, 0)) 'JulioC',
                SUM(IF(MONTH(sbc.fecha)=8, sbc.CantidadVentas, 0)) 'AgostoV',
                SUM(IF(MONTH(sbc.fecha)=8, sbc.CantidadCompras, 0)) 'AgostoC',
                SUM(IF(MONTH(sbc.fecha)=9, sbc.CantidadVentas, 0)) 'SeptiembreV',
                SUM(IF(MONTH(sbc.fecha)=9, sbc.CantidadCompras, 0)) 'SeptiembreC',
                SUM(IF(MONTH(sbc.fecha)=10, sbc.CantidadVentas, 0)) 'OctubreV',
                SUM(IF(MONTH(sbc.fecha)=10, sbc.CantidadCompras, 0)) 'OctubreC',
                SUM(IF(MONTH(sbc.fecha)=11, sbc.CantidadVentas, 0)) 'NoviembreV',
                SUM(IF(MONTH(sbc.fecha)=11, sbc.CantidadCompras, 0)) 'NoviembreC',
                SUM(IF(MONTH(sbc.fecha)=12, sbc.CantidadVentas, 0)) 'DiciembreV',
                SUM(IF(MONTH(sbc.fecha)=12, sbc.CantidadCompras, 0)) 'DiciembreC',
                SUM(sbc.CantidadVentas) 'Total Ventas',
                SUM(sbc.CantidadCompras) 'Total Compras'
FROM (
SELECT a1.codigo, a1.nombre, 
             c.precio precioCompra, 
             NULL as precioVenta,
             c.fecha,
             c.cantidad as "CantidadCompras", 
             0 as "CantidadVentas"
FROM articulo a1 INNER JOIN compra c ON a1.claart = c.claart
WHERE YEAR(c.fecha)=2012
UNION ALL
SELECT a2.codigo, a2.nombre,
             NULL as precioCompra, 
             v.precio as precioVenta, 
             v.fecha,
             0 as "CantidadCompras", 
             v.cantidad as "CantidadVentas"
FROM articulo a2 INNER JOIN venta v ON a2.claart = v.claart
WHERE YEAR(v.fecha)=2012) as sbc
GROUP BY sbc.codigo
 

No es compleja es una consulta sobre una subconsulta (sbc) que a su vez es una union de consultas.

SELECT .... FROM (SELECT... UNION ALL SELECT...) as sbc WHERE... GROUP BY....

Ahora funcionará....he cambiado v.fecha por sbc.fecha en el primer select ....

El precio medio debe ser ponderado.... SUMA(precio*cantidad)/SUMA(cantidad).

Código MySQL:

SELECT a1.codigo, a1.nombre, 
             SUM(c.precio*c.cantidad)/SUM(c.cantidad) PrecioMedioCompras
FROM articulo a1 INNER JOIN compra c ON a1.claart = c.claart
WHERE YEAR(c.fecha)=2012
GROUP BY c.codigo
 

[alyciashape]

Muchísimas gracias Quim, ahora si que funciona. Y además he entendido lo del FROM cosa que no sabía usar solo con ese pequeño comentario que añadiste.

Esto equivale a hacer una vista y hacer una consulta a esa vista no? lo de SELECT campos FROM (SELECT campos). El rendimiento sería similar o compensa hacer una vista?

[alyciashape]

No quería abusar pero pensando en lo de FROM () estaba pensando si esto podría funcionar? Es sólo una prueba:

Probablemente sea un disparate ya que ni lo he probado que no estoy en casa. Son 2 tablas idénticas. Compras y ventas. Si solo quisiera saber la diferencia de compras-ventas podría hacer algo así?

Código MySQL:

SELECT claart, SUM(cantidad) 'cantidad' FROM (
SELECT claart, claemp, SUM(cantidad) 'cantidad'
FROM compra WHERE year(fecha)<2011
UNION
SELECT claart, claemp, SUM(cantidad) 'cantidad'
FROM venta WHERE year(fecha)<2011
)
 

[quimfv]

Código MySQL:

SELECT claart, SUM(sbc.cantidadC), 
                        SUM(sbc.cantidadV),  
                        SUM(sbc.cantidadC)-SUM(sbc.cantidadV) 'Saldo' 
FROM (
SELECT claart, claemp, cantidad 'cantidadC',0  'cantidadV'
FROM compra WHERE year(fecha)<2011
UNION ALL
SELECT claart, claemp, 0  'cantidadC', cantidad 'cantidadV'
FROM venta WHERE year(fecha)<2011
) as sbc
GROUP BY claart;
 

Si hay mas de un claemp por cada claart deberias agregarlo al group by....

Esto equivale a hacer una vista y hacer una consulta a esa vista no? lo de SELECT campos FROM (SELECT campos). El rendimiento sería similar o compensa hacer una vista?

Equivalente del todo no pero es similar....

Una vista es una consulta guardada por decirlo de alguna manera, es eficiente si esa consulta la vas a usar habitualmente... si no hay diferencia... en mysql el uso forzado de indices que se puede hacer en una consulta para mejorar el rendimiento, no se puede usar en una vista... almenos hasta las versiones que he tocado... no se si ha cambiado.