sum in two tables in a single query

Asked

Viewed 226 times

-1

Have two queries, one in each table.

select SUM(quantidade) as total1 FROM tbmateria GROUP by codigomateria
select SUM(quantidade) as total2 FROM tbmateriaestoque GROUP by codigomateria

need to do (total2 - total1), but has to be a single query.

I tried to do so, but without success

SELECT
(SUM(tbmateria.quantidade) - SUM(tbmateriaestoque.quantidade)) as soma4,tbmateria.nomemateria

FROM tbmateria INNER JOIN tbmateriastock ON(tbmateria.codigomateria = tbmateriastock.codigomateria)

GROUP BY tbmateria.codigomateria.

php mysql

  • see if this answers your question: https://answall.com/q/454569/57220

  • What do you mean by "unsuccessful"? Gave error? The result was not expected?

  • the calculations are wrong. appears a very high numbers, for example: 1537406.250

1 answer

-1


Hello,

See if it solves, but there are some validation problems yet! If there is no code backup in the 2 tables!

SELECT 

t.codigomateria, 
(
(SELECT SUM(tte.quantidade) FROM tbmateriaestoque tte WHERE tte.codigomateria = t.codigomateria GROUP BY tte.codigomateria)
-
(SELECT SUM(tt.quantidade) FROM tbmateria tt WHERE tt.codigomateria = t.codigomateria  GROUP BY tt.codigomateria)
) AS diferenca

FROM tbmateria t 
JOIN tbmateriaestoque te ON (t.codigomateria = te.codigomateria)
GROUP BY t.codigomateria

In this case, would bring all codes... to filter, add a WHERE at the end!

I couldn’t test it... but I think it’ll work!

  • Perfect, I only had to put GROUP BY t.codigomateria at the end. thank you very much.

  • I think that would be necessary if t.codigomateria is not UNIQUEKEY... but I’ll update the answer!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.