[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SUM of colums in SQL

Posted on 2012-04-03
4
Medium Priority
?
281 Views
Last Modified: 2012-04-04
I am banging my head against my desk trying to figure this out!!!

I have 5 tables. Each has a similar ID to go off of and a similar column that contains a 'money' field called Total...

I am trying to get the sum for these totals..

All I want is all of them added together.
 Some tables will contain more than one column (With the same ID) and some may be NULL.

All I need is a SELECT case that returns ONE Row and One Column with All the totals added up.

Table Examples:
Table_1
ID    Total
1      8.00
1     10.00

Table_2
ID    Total
1      4.00
2      3.50

Table_3
ID    Total
1      6.00

Table_4
ID    Total
        NULL

Table_5
ID    Total
1      2.00

The query I need would return 30

Please Help!!!!
0
Comment
Question by:natethegreat1977
4 Comments
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 37804754
Hello natethegreat1977,

What about this:
;WITH t AS 
(
SELECT * FROM Table_1
UNION ALL
SELECT * FROM Table_2
)
SELECT SUM(Total) FROM t

Open in new window

0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37804761
0
 
LVL 15

Expert Comment

by:gplana
ID: 37804778
Try this:
SELECT SUM(total)
FROM (
   SELECT total
   FROM table1
   WHERE ID=1
   UNION
   SELECT total
   FROM table2
   WHERE ID=1
   UNION
   SELECT total
   FROM table3
   WHERE ID=1
   UNION
   SELECT total
   FROM table4
   WHERE ID=1
   UNION
   SELECT total
   FROM table5
   WHERE ID=1
);

Open in new window


Hope it helps.
0
 

Author Closing Comment

by:natethegreat1977
ID: 37804792
REALLY!!

I tried something really close to this about 50 time but never completed it with the SELECT statement at the end so I would never get one row!!

I knew it was something simple and I am surprised it was so hard to find information about this topic on the web!

I had to modify the * because for some reason it didn't work with that..
Here is the complete query:
;WITH t AS
(
SELECT Total FROM ylu_Pizza_Order
WHERE OrderID = @OrderID
UNION ALL
SELECT Total FROM ylu_Calzone_Order
WHERE OrderID =  @OrderID
UNION ALL
SELECT Total FROM ylu_Salad_Order
WHERE OrderID =  @OrderID
UNION ALL
SELECT Total FROM ylu_Side_Order
WHERE OrderID =  @OrderID
UNION ALL
SELECT Total FROM ylu_Soda_Order
WHERE OrderID =  @OrderID
)
SELECT SUM(Total) FROM t

Thanks a million Rimvis. I can now go to bed!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 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