Sql Query

Hi all i have created the following query

SELECT     SUM(OutstandingValue) AS Sum_OutstandingValue
FROM         dbo.V_TotalBackLog
WHERE     (it_due <= '06-30-2012')

union all

SELECT     SUM(sa_trvalue) AS Sum_invoiceValue
FROM         dbo.V_InvoiceValue
WHERE     (sa_trdate >= '07-01-2011') AND (sa_trdate <= '06-30-2012')

i think i have used the wrong function basically what i want to do is to add the sum from the first query to the sum of the the secound query to give me a total figure.

i have used the union all which places both values on seperate rows i just want the one value of both added together.

john
pepps11976Asked:
Who is Participating?
 
CluskittCommented:
Sorry, my bad. You have to name both the same, so it would have to be:
SELECT SUM(Sum_OutstandingValue)
FROM (SELECT     SUM(OutstandingValue) AS Sum_OutstandingValue
FROM         dbo.V_TotalBackLog
WHERE     (it_due <= '06-30-2012')

union all

SELECT     SUM(sa_trvalue) AS Sum_OutstandingValue
FROM         dbo.V_InvoiceValue
WHERE     (sa_trdate >= '07-01-2011') AND (sa_trdate <= '06-30-2012')) t
0
 
CluskittCommented:
SELECT Sum_OutstandingValue+Sum_invoiceValue
FROM (SELECT     SUM(OutstandingValue) AS Sum_OutstandingValue
FROM         dbo.V_TotalBackLog
WHERE     (it_due <= '06-30-2012')

union all

SELECT     SUM(sa_trvalue) AS Sum_invoiceValue
FROM         dbo.V_InvoiceValue
WHERE     (sa_trdate >= '07-01-2011') AND (sa_trdate <= '06-30-2012')) t
0
 
pepps11976Author Commented:
Hi

I get the follwoing error

Invalid column name 'Sum_invoicevalue' with the above

John
0
 
pepps11976Author Commented:
Brilliant Thankyou
0
 
plummetCommented:
You could try it this way:


Declare @totalValue numeric

SELECT     @totalValue = SUM(OutstandingValue) AS Sum_OutstandingValue
FROM         dbo.V_TotalBackLog
WHERE     (it_due <= '06-30-2012')

SELECT     @totalValue= @totalValue + SUM(sa_trvalue) AS Sum_invoiceValue
FROM         dbo.V_InvoiceValue
WHERE     (sa_trdate >= '07-01-2011') AND (sa_trdate <= '06-30-2012')

select  @totalValue

Open in new window

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.