Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SUM of colums in SQL

Posted on 2012-04-03
Medium Priority
280 Views
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

0
Question by:natethegreat1977
[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

LVL 19

Accepted Solution

Rimvis earned 2000 total points
ID: 37804754
Hello natethegreat1977,

``````;WITH t AS
(
SELECT * FROM Table_1
UNION ALL
SELECT * FROM Table_2
)
SELECT SUM(Total) FROM t
``````
0

LVL 17

Expert Comment

ID: 37804761
0

LVL 15

Expert Comment

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
);
``````

Hope it helps.
0

Author Closing Comment

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

Question has a verified solution.

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

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â€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month10 days, 15 hours left to enroll