Solved

SUM of colums in SQL

Posted on 2012-04-03
4
269 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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now