SQL Help with Sum from 2 different tables

I'm trying to combine two SQL statments to find for an item:
1. How many units were sold (in the invoiceline table)
2. How many units were returned (in the creditmemoline table)

select sum(InvoiceLineQuantity) as QSold from InvoiceLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and InvoiceLineItemRefFullName = 'CB:CB001' 

Open in new window


select sum(CreditmemoLineQuantity) as CMSold from CreditmemoLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and CreditMemoLineItemRefFullName = 'CB:CB001'

Open in new window


Is it possible to combine these two statments together?  The only thing they really have in common is the ItemRefFullName -

This loops through a bunch of items, so I'd rather only make one call the the datbase each time if possible.

Thanks!
LVL 1
slightlyoffAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
if you have another table (which I presume) which has the itemFillnames...

select t.InvoiceLineItemRefFullName 
 , ( select sum( il.InvoiceLineQuantity) 
    from InvoiceLine il  
   where il.txnDate <= #11/1/2012# and il.txnDate >= #8/3/2012# and   il.InvoiceLineItemRefFullName = t.InvoiceLineItemRefFullName
 )  as QSold 
, ( select sum(cl.CreditmemoLineQuantity) 
    from CreditmemoLine cl
  where cl.txnDate <= #11/1/2012# and cl.txnDate >= #8/3/2012# and cl.CreditMemoLineItemRefFullName = t.InvoiceLineItemRefFullName
 ) as CMSold 
from your_table t 

Open in new window


you may want to use the DSUM() function instead, eventually, but normally that will result in less efficient code.

if you don't have such a table, you should do that...
0
 
jerryb30Commented:
select sum(InvoiceLineQuantity) as QSold, "QSold" as Type from InvoiceLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and InvoiceLineItemRefFullName = 'CB:CB001'
Union All
select sum(CreditmemoLineQuantity) as CMSold, "CMSold" as Type from CreditmemoLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and CreditMemoLineItemRefFullName = 'CB:CB001'
0
 
Rey Obrero (Capricorn1)Commented:
you can create a union query like this


select 'InvoiceLine' As Source,sum(InvoiceLineQuantity) as QSold from InvoiceLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and InvoiceLineItemRefFullName = 'CB:CB001'
union
select 'CreditmemoLine' As Source,sum(CreditmemoLineQuantity) as QSold from CreditmemoLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and CreditMemoLineItemRefFullName = 'CB:CB001'
0
 
slightlyoffAuthor Commented:
Thanks for the quick reply!

I tried:
"select t.FullName, ( select sum( il.InvoiceLineQuantity) from InvoiceLine il  where(il.txnDate <= #11/1/2012# And il.txnDate >= #8/3/2012# And il.InvoiceLineItemRefFullName = t.FullName))  as QSold, ( select sum(cl.CreditmemoLineQuantity) from CreditmemoLine cl where(cl.txnDate <= #11/1/2012# And cl.txnDate >= #8/3/2012# And cl.CreditMemoLineItemRefFullName = t.FullName)) as CMSold from ItemInventory t where t.fullName = 'CB:CB002'"

Open in new window


And it worked in Access, but in VB.NET at the line I fill the dataAdapter:
da2.Fill(ds2, "table1")

Open in new window


I get the following error:
Conversion failed because the Decimal data value overflowed the type specified for the Decimal value part in the consumer's buffer.

I'm not sure what decimal value this would be talking about, since i don't have decimals in the database...  and I don't know what the consumer's buffer means...  is this an OleDB issue?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.