How do I sum the results of two queries? 1

[Snakeroot]

I'm not sure what to search for on this question, so I'm just going to ask the question.

I have two queries built and I want the results to combine the AccountRef_FullName categories together.

Here is the first query:

SELECT SOPD.AccountRef_FullName, SUM(ILD.Amount) AS SumTotal
FROM invoicelinedetail ILD
INNER JOIN Invoice ON ILD.IDKey = Invoice.TxnID 
INNER JOIN ItemService ON ItemService.ListID = ILD.ItemRef_ListID
INNER JOIN SalesorPurchaseDetail SOPD ON SOPD.IDKEY = ItemService.ListID
WHERE (Invoice.TxnDate >= '2/1/2008' AND Invoice.TxnDate <= '3/1/2008')
GROUP BY SOPD.AccountRef_FullName

Here is the second query:

SELECT SOPD.AccountRef_FullName, SUM(ABS(CMLD.Amount)) AS SumTotal
FROM creditmemolinedetail CMLD
INNER JOIN CreditMemo ON CMLD.IDKey = CreditMemo.TxnID 
INNER JOIN ItemService ON ItemService.ListID = CMLD.ItemRef_ListID
INNER JOIN SalesorPurchaseDetail SOPD ON SOPD.IDKEY = ItemService.ListID
WHERE CreditMemo.TxnDate >= '2/1/2008' AND CreditMemo.TxnDate <='3/1/2008'
GROUP BY SOPD.AccountRef_FullName
ORDER BY SOPD.AccountRef_FullName

When I run these two queries with a "UNION ALL"
this is the current result I'm getting:

AccountRef_FullName       SumTotal
Services:Disposal Fee     3703
Services:Other Services   370
Services:Recovery         17725
Services:Tank Service     210
Services:Tank Service     8398

In the results above, the resulting line "Services:Tank Service 210" is the only thing coming from the second query (which is correct). However, I would like the results from query one to be combined with the results from query two when the AccountRef_FullName is the same.

So the desired results would look like this:

AccountRef_FullName       SumTotal
Services:Disposal Fee     3703
Services:Other Services   370
Services:Recovery         17725
Services:Tank Service     8608

Can anyone point me in the right direction on getting this result? Thanks!

 

[klewis10367]

You could create a third view off the union view and sum it again.

 

[chamilz]

you could try this.

select a.AccountRef_FullName,sum(a.SumTotal)SumTotal
from ( 'your first query'
       union all
       'your Second query'
      ) a
group by  a.AccountRef_FullName

 

[Snakeroot]

Perfect! Thanks!

 

[ESquared]

For what it's worth, it looks like your Invoice and CreditMemo tables might have benefited from being one table, with a supertype/subtype relationship if necessary to resolve different data collection.

In the meantime, may I suggest the following? Try it out for performance:

SELECT SOPD.AccountRef_FullName, SUM(A.Amount) AS SumTotal
FROM invoicelinedetail ILD
INNER JOIN (
   SELECT IDKey, Amount
   FROM Invoice
   WHERE
      Invoice.TxnDate >= '2/1/2008'
      AND Invoice.TxnDate <= '3/1/2008' [red]--are you sure you didn't mean only less than?[/red]
   UNION ALL SELECT IDKey, Amount
   FROM CreditMemo 
   WHERE
      CreditMemo.TxnDate >= '2/1/2008'
      AND CreditMemo.TxnDate <= '3/1/2008'
) A ON A.IDKey = Invoice.TxnID 
INNER JOIN ItemService ON ItemService.ListID = ILD.ItemRef_ListID
INNER JOIN SalesorPurchaseDetail SOPD ON SOPD.IDKEY = ItemService.ListID
GROUP BY SOPD.AccountRef_FullName

Of course, you don't need all those other table for the part you're showing, but I imagine you gave us a simplified version.

In fact, it's probably better to alter the query to move the grouping into the part that needs grouping, so that you're not multiplying records needlessly:

SELECT SOPD.AccountRef_FullName, A.Amount AS SumTotal
FROM invoicelinedetail ILD
INNER JOIN (
   SELECT IDKey, Amount = Sum(Amount)
   FROM (
      SELECT IDKey, Amount = Sum(Amount)
      FROM Invoice 
      WHERE
         Invoice.TxnDate >= '2/1/2008'
         AND Invoice.TxnDate <= '3/1/2008'
      UNION ALL SELECT IDKey, Amount = Sum(Amount)
      FROM CreditMemo 
      WHERE
         CreditMemo.TxnDate >= '2/1/2008'
         AND CreditMemo.TxnDate <= '3/1/2008'
   ) X
) A ON A.IDKey = Invoice.TxnID 
INNER JOIN ItemService ON ItemService.ListID = ILD.ItemRef_ListID
INNER JOIN SalesorPurchaseDetail SOPD ON SOPD.IDKEY = ItemService.ListID
GROUP BY SOPD.AccountRef_FullName

Further optimizations are probably possible, but the core of the problem is having transaction data split between two tables...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]