Solved

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

Posted on 2007-11-20
6
1,096 Views
Last Modified: 2010-08-05
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
Comment
Question by:sparky74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20319202
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
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 500 total points
ID: 20319204
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 20319208
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!

 
LVL 31

Expert Comment

by:James Murrell
ID: 20319240
whoops i knew i forgot something...... ignore my comments....
0
 

Author Closing Comment

by:sparky74
ID: 31410100
thanks for the quick response
0
 

Author Comment

by:sparky74
ID: 20319253
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
SQL Percentage Formula 7 33
SQL: get ride of blank rows 11 23
Populate fields in group based on first record in group 4 16
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question