Search code examples
sqlsum

SQL Sum two rows with the same ID

I have two tables (orders and transfers) and I would like to sum up only the amount in casese where there are multiple transfers per order.

Thsi is my SQL. The sum works correctly, but SQL still returns two lines - I would just liek to see one line.

SQL:

Select
OrderId,
CustomerId,
SUM(Amount) over (partition by O.OrderId) AS Amount
FROM Orders O
LEFT JOIN Transfers T ON O.OrderId = T.OrderId

Orders:

OrderId CustomerId
Order_1 Customer_1

Transfers:

TransferID Amount OrderId
Transfer_1 5 Order_1
Transfer_2 5 Order_1

This is my expected result:

OrderId Customer_Id Amount
Order_1 Customer_1 10

But what I get is:

OrderId Customer_Id Amount
Order_1 Customer_1 10
Order_1 Customer_1 10
Solution

  • You need a basic GROUP BY:

    SELECT
    o.OrderId,
    o.CustomerId,
    SUM(t.Amount) AS Amount
FROM Orders o
LEFT JOIN Transfers t ON o.OrderId = t.OrderId
GROUP BY
    o.OrderId,
    o.CustomerId;