SQL SERVER - Calculus

Asked

Viewed 89 times

1

I have a question to put together a panel of indicators and I’m not getting out of it.

I basically have two tables.

Table 1: CONTRACTS
Fields: Company ID, Contract ID, Product ID, Quantity

Table 2: RECEIPTS
Fields: Company ID, Receipt ID, Contract ID, Quantity

With that I set up this query

SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) Total
 FROM 
    Contrato A
 WHERE 
    A.Registro='Ativo'
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa

This query gives me a correct result.

To try to bring me the result of the total received from the contracts I set up the following query.

SELECT
        A.ID_Empresa,
        A.ID_Produto,
        SUM(CAST(A.Quantidade AS int)) Total
     FROM 
        Contrato A
LEFT JOIN Recebimentos B ON B.ID_Contrato=A.ID_Contrato 
     WHERE 
        A.Registro='Ativo'
    GROUP BY
        A.ID_Empresa,
        A.ID_Produto
    ORDER BY
        A.ID_Empresa

However, when I try to make this request with the RECEIPTS table to know how much was received, the query is adding up the contract quantity each time it appears on the receiving line.

Is there any way to keep these accounts separate and not double the sum of contracts?

sql sql-server

  • You can write an example of the table data, and an example of the expected result ?

  • Yes. In the first query my result is this: ID Company | ID Product | Total 0009 | 0000001 | 11470000 0009 | 0000002 | 800000 0012 | 0000001 | 400000 0017 | 0000001 | 4933540 0017 | 0000002 | 8000000 But with Join it looks like this ID Company | ID Product | Total 0009 | 0000001 | 60090000 0009 | 0000002 | 800000 0012 | 0000001 | 400000 0017 | 0000001 | 62865120 0017 | 0000002 | 8000000

  • My answer worked out ?

  • Unfortunately not Mark. With its answer the query filtered only the contracts with receipt. I need to keep account of all contracts because I will add the amount received.

  • what would be the correct result of the query with Join ?

2 answers

3


From what I understand of your question, it would be that answer:

SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) Total
 FROM 
    Contrato A
 WHERE 
    A.Registro='Ativo'
    AND A.ID_Contrato IN ( SELECT ID_Contrato  FROM Recebimentos  )
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa

Contract Comparison and Receipt:

 SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) AS TotalContrato,
    ValorRecebido
 FROM 
    Contrato A
 LEFT JOIN (
    SELECT 
        ID_Empresa,
        ID_Produto,
        SUM(CAST(Quantidade as int)) as ValorRecebido
    FROM RECEBIMENTOS
    GROUP BY
        ID_Empresa,
        ID_Produto
 ) AS B
 ON B.ID_Contrato=A.ID_Contrato 

 WHERE 
    A.Registro='Ativo'
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa

  • It is that after actually I still need to add a sum of the field Quantity only that of the receipts table. For me to have a comparison between hired X Received. If I do so, I limit the account to only the contracts that have been received. I need the quantity of all contracts

  • And now, you got what you wanted ?

  • Thank you very much Rafael. It worked. The sum was wrong because the cardinality of the tables were different. Leaving the tables with the same cardinality the sum stopped duplicating. It worked very well. Thanks!!

1

You didn’t put example data, not even an example of how the correct result would be, yet I recreated your tables in the fiddle and put some data. I believe that your need is to know Contracted Quantity / Received Quantity and by the example I created, this query solves your problem. See:

   SELECT
A.ID_Empresa,
A.ID_Produto,
A.id_contrato,
SUM(CAST(A.Quantidade AS int)) TotalContratado,
sum(coalesce(r.quantidade,0)) as totalRecebido
FROM 
    Contratos A
 LEFT OUTER JOIN recebimentos r on r.id_contrato = a.id_contrato   
GROUP BY
    A.ID_Empresa,
    A.ID_Produto,
    A.id_contrato
ORDER BY
    A.ID_Empresa;

Check on the Sqlfiddle: http://sqlfiddle.com/#! 6/490522/2

Browser other questions tagged sql sql-server

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