Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

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

0
sparky74
Asked:
sparky74
1 Solution
 
James MurrellProduct SpecialistCommented:
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')
0
 
Ashish PatelCommented:
Try this
Select Sum(Total) From (
select SUM(grandtotal) AS Total  FROM   dbo.savedorders  WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y'
Union
SELECT SUM(grandtotal) As total
FROM dbo.orders
WHERE DATEDIFF (d, orderdate, getdate()) = 0 AND cardvaild ='y' ) A

Open in new window

0
 
mankowitzCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
James MurrellProduct SpecialistCommented:
whoops i knew i forgot something...... ignore my comments....
0
 
sparky74Author Commented:
thanks for the quick response
0
 
sparky74Author Commented:
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now