Add summed column to results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add summed column to results

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2007-07-18 : 13:54:24

This is my query which is simple

SELECT distinct sum_object, batch_date, sub_object, vendor_name, invoice_no, invoice_desc, amount, sum_object_desc, sub_object_desc, budget, ytdspent
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid

I am trying to add a sum(distinct budget), sum(distinct ytdspent) fields to the results. If I run this query I get the results I want.

Here is that query

SELECT sum(distinct budget), sum(distinct ytdspent)
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid
group by substring(sum_object,1,1)

I need a way to combine these results in one recordset. If I use the group by statement in the second query for the first query the grouping is wrong. I know I can use union but I don't have equal number of columns.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 13:56:38

You can put 0 or NULL in the second SELECT to make up for the missing columns.. if it makes it easier..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-18 : 14:06:37

Forgive me for asking, but why SUM(DISTINCT Budget)?
What if two objects actually have the same budget?

I think you better rewrite query using a derived table.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-18 : 14:15:53

Or add a correlated subquery.
SELECT distinct sum_object, batch_date, sub_object, vendor_name, invoice_no, invoice_desc, amount, sum_object_desc, sub_object_desc, budget, ytdspent, (SELECT SUM(Budget) FROM SomeTable WHERE SomeTable.SomeCol = OtherTable.OtherCol) AS SumBud,
(SELECT SUM(YtdSpent) FROM SomeTable2 WHERE SomeTable2.SomeCol = OtherTable2.OtherCol) AS SumYtdSpent
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid

Peter Larsson
Helsingborg, Sweden

forwheeler
Starting Member

44 Posts

Posted - 2007-07-18 : 14:19:41

quote:
Originally posted by Peso

Forgive me for asking, but why SUM(DISTINCT Budget)?
What if two objects actually have the same budget?

I think you better rewrite query using a derived table.

Peter Larsson
Helsingborg, Sweden

I am doing the distinct because I don't want to sum all the budget numbers but only the distinct ones. A sum_object is a number such as 4101, 4201, 5001, 5151, 5351 etc. I want the sum of all the 4xxx and sum all the 5xxx and not sum of all the records. That is why I have the group by substring(sum_object,1,1)

I would like to see the query for the derived table if you have one.

forwheeler
Starting Member

44 Posts

Posted - 2007-07-18 : 15:54:01

quote:
Originally posted by Peso

Or add a correlated subquery.
SELECT distinct sum_object, batch_date, sub_object, vendor_name, invoice_no, invoice_desc, amount, sum_object_desc, sub_object_desc, budget, ytdspent, (SELECT SUM(Budget) FROM SomeTable WHERE SomeTable.SomeCol = OtherTable.OtherCol) AS SumBud,
(SELECT SUM(YtdSpent) FROM SomeTable2 WHERE SomeTable2.SomeCol = OtherTable2.OtherCol) AS SumYtdSpent
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid

Peter Larsson
Helsingborg, Sweden

This gives the wrong result since there is no grouping. I need to group by substring(sum_object,1,1) for the sum columns but I want the all the rows for the other columns which could be group by sum_object.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 01:49:44

I think now is the time to post some proper sample data and your expected output based on the provided sample data!

Peter Larsson
Helsingborg, Sweden

forwheeler
Starting Member

44 Posts

Posted - 2007-07-19 : 08:56:10

Thanks for your help so far...

@tsumobj contains sum_object_desc
@tbudget contains budget
@ytdspent contains ytdspent
@tobjectdetail contains all other fields

SELECT distinct sum_object, batch_date, sub_object, vendor_name, invoice_no,
invoice_desc, amount, sum_object_desc, sub_object_desc, budget, ytdspent
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid

Subset of results:

4101 00:00.0 4105 removed removed removed 5201 sum_object_desc sub_object_desc 0 0
4101 00:00.0 4115 removed removed removed 564 sum_object_desc sub_object_desc 0 0
4201 00:00.0 4215 removed removed removed 5.36 sum_object_desc sub_object_desc 14000 65000.8
4201 00:00.0 4215 removed removed removed 5.24 sum_object_desc sub_object_desc 14000 65000.8
4201 00:00.0 4215 removed removed removed 2.89 sum_object_desc sub_object_desc 14000 65000.8
5001 00:00.0 5029 removed removed removed 871 sum_object_desc sub_object_desc 5000 9000.4
5001 00:00.0 5030 removed removed removed 2100 sum_object_desc sub_object_desc 5000 9000.4
5151 00:00.0 5199 removed removed removed 9876 sum_object_desc sub_object_desc 18000 10000
5151 00:00.0 5190 removed removed removed 478 sum_object_desc sub_object_desc 18000 10000
5351 00:00.0 5396 removed removed removed -89 sum_object_desc sub_object_desc 8500 1549
5401 00:00.0 5410 removed removed removed 384.52 sum_object_desc sub_object_desc 980 600
5551 00:00.0 5580 removed removed removed 247 sum_object_desc sub_object_desc 11000 590
5901 00:00.0 5920 removed removed removed 89 sum_object_desc sub_object_desc 8000 50.5
6401 00:00.0 6420 removed removed removed 558 sum_object_desc sub_object_desc 0 400

SELECT sum(distinct budget), sum(distinct ytdspent)
FROM @tobjectdetail o
INNER JOIN @tbudget b ON o.tid = b.tid
INNER JOIN @tytdspent ytds ON o.tid = ytds.tid
group by substring(sum_object,1,1)

14000.00 65000.80
51480.00 21789.90
0.00 400.00

With duplicate budget and ytdspent values in the second query, to get the total budget and ytdspent, I need to use distinct so I don't add the duplicate values.

I want to be able to combine the results of both queries.

(I can't seem to get the formating right when I paste the results here for posting)

forwheeler
Starting Member

44 Posts

Posted - 2007-07-24 : 09:32:57

I got it to where I needed it.
I added the sum_object to the table variables and then used this query:

SELECT distinct sum_object, batch_date, sub_object, vendor_name, invoice_no, invoice_desc, amount,
sum_object_desc, sub_object_desc, (SELECT SUM(distinct Budget) FROM @tbudget b WHERE substring(b.sumobject,1,1) = substring(o.sum_object,1,1) group by substring(b.sumobject,1,1)) AS budgetsum,
(SELECT SUM(distinct YtdSpent) FROM @tytdspent ytd WHERE substring(ytd.sumobject,1,1) = substring(o.sum_object,1,1) group by substring(ytd.sumobject,1,1)) AS ytdsum
FROM @tobjectdetail o
INNER JOIN @tsumobj sumobj ON o.tid = sumobj.tid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 12:02:22

You went for the "correlated subquery" thingies anyway!
Good for you

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources