Summing Two Columns with a Join in SQL Server

Question

I'm having a bit of issue trying to sum two column together with a left join - running in to grouping problems.

An example of the issue is below:

Table One: [Order]

ID  CustomerID      
1   512         
2   317         
3   562

Table Two: [OrderEntry]

OrderID     Type    ID  QuantitySold    QuantityReturned
1           A       1   1               0
1           A       2   3               0
1           A       3   1               1
2           A       4   1               1
3           B       5   2               0

What I'm trying to display:

CustomerID  ID  Sold - Returned     
512         1   1       
512         1   3       
512         1   0       
317         2   0

Where [OrderEntry].Type = 'A'

Kamil Gosciminski · Accepted Answer · 2016-04-09 11:29:23Z

2

This is very basic SQL:

SELECT
    ord.CustomerID
  , ord.ID
  , orden.QuantitySold - orden.QuantityReturned AS [Sold - Returned]
FROM Order ord
LEFT JOIN OrderEntry orden 
  ON ord.ID = orden.ID
WHERE orden.Type = 'A'

answered Apr 9, 2016 at 11:29

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Revokez Over a year ago

Thanks - that's exactly it. For some reason I had it stuck in my head that this was a SUM.

Kamil Gosciminski Over a year ago

If you'd like to group it by customer and/or order id then you would use a sum to get one row for each group.

Abhishek Gurjar · Accepted Answer · 2016-04-09 11:36:39Z

-1

Here you can use any join as you are using and use concat function on two of your column like this

select concat(OrderEntry.QuantitySold, OrderEntry.QuantityReturned) AS newcolumn_name

answered Apr 9, 2016 at 11:36

Abhishek Gurjar

7,47610 gold badges41 silver badges47 bronze badges

1 Comment

Mihai-Daniel Virna Over a year ago

This is a string concatenation and has nothing to do with arithmetical operations between two columns.

Collectives™ on Stack Overflow

Summing Two Columns with a Join in SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related