question

gholamrezarezaie-0628 avatar image
0 Votes"
gholamrezarezaie-0628 asked gholamrezarezaie-0628 commented

sum in sql server for 2 table

hello my friends i write a script like this :

select v.itemTitle,v.ItemCode ,
(select SUM (Quantity) from INV.vwInventoryReceiptItem) - (select SUM(quantity) from INV.vwInventoryDeliveryItem )from inv.vwInventoryReceiptItem vinner join INV.vwInventoryDeliveryItem k on k.ItemRef=v.Itemref
group by v.itemTitle,v.ItemCode


my problem is that it have result jus only i have data in both tables but when i dont  have any data in table INV.vwInventoryDeliveryItem  it havent any result 

 i need that when we have no data in table ' INV.vwInventoryDeliveryItem '  result only sum first table

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered gholamrezarezaie-0628 commented

Hi @gholamrezarezaie-0628,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

According to limited information provided, you could use left join instead of inner join and update the query like below:

 select v.itemTitle,v.ItemCode ,
 isnull(SUM (v.Quantity),0) - isnull(SUM(k.quantity),0)
 from inv.vwInventoryReceiptItem v 
 left join INV.vwInventoryDeliveryItem k on k.ItemRef=v.Itemref
 group by v.itemTitle,v.ItemCode

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft


thank you very very much .. you help and teach me a lot . i cant said good thanks for your helping

0 Votes 0 ·