¿Cómo hacer una consulta doble en la misma tabla MySql?

publicado por: Anonymous

Más abajo hago una explicación de lo que hago y de lo que necesito, ahora dejo un planteamiento de la tabla objetivo y la consulta que utilizo.

Tabla objetivo (fact_contabilidad_lineas):

id_producto    | unds   | compra_venta
10517931       | 2      | Compra 
10517929       | 4      | Venta 
10517929       | 2      | Venta
10517939       | 6      | Compra 
10517931       | 3      | Venta 

Esta es mi consulta:

SELECT 
fact_contabilidad_lineas.id_producto, 
SUM(fact_contabilidad_lineas.unds) as unds,
fact_contabilidad.compra_venta

FROM fact_contabilidad, fact_contabilidad_lineas

WHERE fact_contabilidad_lineas.serie = concat_ws('-', fact_contabilidad.id_serie, fact_contabilidad.serie_num)
AND fact_contabilidad_lineas.iduser = 98
AND fact_contabilidad.iduser = 98
AND fact_contabilidad.compra_venta = 'Venta'
AND fact_contabilidad_lineas.id_producto > 10517927

GROUP BY fact_contabilidad_lineas.id_producto

Resultado:

id_producto    | unds   | compra_venta
10517929       | 6      | Venta 
10517931       | 3      | Venta 

No he mostrado la tabla (fact_contabilidad) porque no es necesario, no influye en mi pregunta y de este modo queda algo más limpia la pregunta. La consulta funciona bien pero solo me saca las lineas que son compra_venta = Venta.

Lo que necesito sacar es:

1 columna la suma de los productos agrupados por id_producto y compra_venta = Venta.

1 columna la suma de los productos agrupados por id_producto y compra_venta = Compra.

Resultado esperado:

id_producto    | Venta | Compra
10517929       | 6     | 6
10517931       | 3     | 2

¿Me pueden orientar? No se me ocurre como hacerlo.

solución

Estás buscando algo así:

SELECT id_producto,
  SUM(if(compra_venta='Venta',unds,0)) Venta,
  SUM(if(compra_venta='Compra',unds,0)) Compra
  FROM fact_contabilidad_lineas
  WHERE id_producto > 10517927
  GROUP BY 1;

En primer lugar, por favor, modifica tu pregunta y pásanos los datos en SQL, de esta forma será mucho más fácil reproducir tu problema:

CREATE TABLE fact_contabilidad_lineas(
  id_producto int,
  unds int,
  compra_venta varchar(31)
  );

INSERT INTO fact_contabilidad_lineas (id_producto, unds, compra_venta) VALUES
(10517931,2,'Compra'),
(10517929,4,'Venta'),
(10517929,2,'Venta'),
(10517929,6,'Compra'),
(10517931,3,'Venta');

Así mismo, si ves que la tabla fact_contabilidad no aporta mucho, elimínala de tu consulta. Así no tendremos que modificarla para hacer las pruebas:

SELECT id_producto,SUM(unds) unds,compra_venta
  FROM fact_contabilidad_lineas
  WHERE compra_venta='Venta' AND id_producto>10517927
  GROUP BY id_producto;

Una vez en este punto, ya podremos ayudarte. Por cierto, muy bien que hayas puesto el resultado esperado. Así hemos podido ver que uno de los datos que nos pasas tenían una errata: el cuarto registro termina en 39 en vez de en 29, recuerda modificarlo en tu dataset para obtener los mismos resultados.

Y ya, por fin, la explicación del SQL: realmente casi lo tenías, habías hecho los grupos con GROUP_BY y los totales con SUM; el único fallo había sido hacer una selección únicamente de las ventas y dejar fuera a las compras. Eliminando esa condición del WHERE ya podías haber utilizado la función IF para sumar únicamente lo que te interesaba en cada columna.

En cualquier caso, a mí no me gusta especialmente utilizar IF porque, de alguna forma, estás obligándote a leer todos los registros de cada grupo. Yo prefiero algo así:

SELECT * FROM (
    SELECT id_producto,SUM(unds) Venta
      FROM fact_contabilidad_lineas
      WHERE compra_venta='Venta'
      GROUP BY 1  
  ) compras JOIN (
    SELECT id_producto,SUM(unds) Compra
      FROM fact_contabilidad_lineas
      WHERE compra_venta='Compra'
      GROUP BY 1  
  ) ventas USING(id_producto);

Parece más larga pero, en casos en los que compra_venta tuviera más de dos valores, sería más eficiente. Además, tiene representación en álgebra relacional, mientras que el IF es más estilo de programador…

Si hay algo que no veas claro, por favor, déjame la duda en los comentarios y ampliaré la respuesta lo que sea necesario pues, cada una esas funciones, tiene bastante miga…

Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *