asked on

ASP MSSQL - SUM() total of 2 columns from 2 tables where date =

Hi, I am trying 2 sum the total value of 2 columns from seperate tables.

I can get the result if both tables have a value, but if my second table named savedorders has no value then i get no value at all returned.

I tried it with union as well, but could work out how to sum the 2 results at the end.

here is my code so far

SELECT (select SUM(grandtotal) AS Total FROM dbo.savedorders WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y') + (SELECT SUM(grandtotal)
FROM dbo.orders
WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y') as today

James Murrell

unsure i think

SELECT (select SUM(grandtotal) AS Total,
(SELECT SUM(grandtotal)
FROM dbo.orders
WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y') as today)

FROM dbo.savedorders WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y')

ASKER CERTIFIED SOLUTION

Ashish Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mankowitz

I think you would have to use a union and do a subquery,

select sum(Total) from
(SELECT (select SUM(grandtotal) AS Total FROM dbo.savedorders WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y')
UNION SELECT SUM(grandtotal)
FROM dbo.orders
WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y') as derivedtable

James Murrell

whoops i knew i forgot something...... ignore my comments....

sparky74

ASKER

thanks for the quick response

sparky74

ASKER

thanks all, asvforce solution was 1st to appear on my screen for some reason and worked just as I needed. It took me a couple of hours getting to where I was, I see now where I went wrong with the select union I was using earlier today.

thanks for all your help