?
Solved

SUM of colums in SQL

Posted on 2012-04-03
4
Medium Priority
?
278 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
[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
  • Learn & ask questions
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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 …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
Suggested Courses

762 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