asked on

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!!!!

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

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

Start Free Trial

Anuroopsundd

require to use union all
http://stackoverflow.com/questions/1388216/getting-the-sum-of-several-columns-from-two-tables

gplana

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.

natethegreat1977

ASKER

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!