How to do a double query on the same MySql table?

You are looking for something like this:

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;

First of all, please, modify your question and pass us the data in SQL, this way it will be much easier to reproduce your problem:

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');

Likewise, if you see that the table fact_contabilidaddoes not contribute much, remove it from your query. So we will not have to modify it to do the tests:

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

Once at this point, we can help you. By the way, very good that you have put the expected result. Thus we have been able to see that one of the data that you pass us had an error: the fourth record ends in 39 instead of 29, remember to modify it in your dataset to obtain the same results.

And now, finally, the explanation of the SQL: you really almost had it, you had made the groups with GROUP_BYand the totals with SUM; the only flaw had been to make a selection only for sales and leave out purchases. Eliminating that condition from WHEREyou could have already used the function IFto add only what you were interested in in each column.

In any case, I don't particularly like to use IFit because, in a way, you're forcing yourself to read all the records in each group. I prefer something like this:

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);

It seems longer, but in cases where it compra_ventahad more than two values, it would be more efficient. Also, it has relational algebra representation, while he IFis more programmer style...

If there is something that you do not see clearly, please, leave me your doubt in the comments and I will expand the answer as necessary, because each of these functions has a lot of crumb...

As you put that result there, you could get it with this query I think:

select id_producto, sum(venta) as venta, sum(compra) as compra 
from [ Aqui juntas las tablas que necesites]
where [ Aqui metes las restricciones que quieras ]
group by id_producto;