SUM of colums in SQL

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!!!!
natethegreat1977Asked:
Who is Participating?
 
RimvisConnect With a Mentor Commented:
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
 
gplanaCommented:
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
 
natethegreat1977Author Commented:
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
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.