Add up the total quantity of a product with different batch? SQL ORACLE

Asked

Viewed 51 times

1

Hello! How are you? I hope everyone is well! Guys I’m new in the business and I believe my problem is easy to solve, but I still have no knowledge to solve and from now I thank everyone for the help!

I am making the query below in a table that returns the amount of output per product and I would like that amount to be summed by product, but the problem is that in this table can occur that for the same product there are two lots, and when this occurs the result returns two lines, how can I make it return only one line with the sum of the two batches of the same product? I have tried subquery and XTAB, but even so it keeps returning the sum per batch and not per product. The strange thing is that in SELECT do not put to return the lot

    SELECT  
        codigoestabelecimento,  
        tipodocumento,  
        seriedocumento,  
        documentosaida,  
        codigoproduto,  
        SUM(quantidadesaida) AS QUANTIDADESAIDA  
   FROM  
        lotesaida  
   WHERE  
        codigoestabelecimento = '1'  
        AND documentosaida = '1313'  
   GROUP BY  
        codigoestabelecimento,  
        tipodocumento,  
        seriedocumento,  
        documentosaida,  
        codigoproduto,  
        quantidadesaida  

1 answer

0


SELECT  
        codigoestabelecimento,  
        tipodocumento,  
        seriedocumento,  
        documentosaida,  
        codigoproduto,  
        SUM(quantidadesaida) AS QUANTIDADESAIDA  
   FROM  
        lotesaida  
   WHERE  
        codigoestabelecimento = '1'  
        AND documentosaida = '1313'  
   GROUP BY  
        codigoestabelecimento,  
        tipodocumento,  
        seriedocumento,  
        documentosaida,  
        codigoproduto

The problem is in grouping, it is grouping by quantity as well, nullifying its aggregator function sum() when the quantities are different. If you still return "duplicate" see carefully the values of the fields that have not been aggregated.

  • Friend indeed the problem was just that, thank you so much for your help!

Browser other questions tagged

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