sum of results & join of ttwo tables

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

Transact-SQL (2005)

sum of results & join of ttwo tables

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-28 : 15:08:02

Table1 has 3 locations = pickat, loadunload, deliverto, depot

table2 has a fromLocation, toLocation, miles

code not working

SELECT c.pickat, c.loadunload, c.deliverto, SUM(c.miles1 + c.miles2)
FROM(
SELECT a.pickat, a.loadunload, a.deliverto, b.miles as miles1
FROM actual_moves a Inner join  
 point2point As b on  a.pickat = b.fromLocation  AND a.loadunload = b.tolocation 
UNION ALL
SELECT a.picakt, a.loadunload, a.deliverto, b.miles as miles2
FROM actual_moves a Inner join  
 point2point As b on  a.loadunload = b.fromLocation  AND a.deliverto = b.tolocation) c
GRoup by c.pickat, c.loadunload, c.deliverto, c.miles

rweasel6
Starting Member

6 Posts

Posted - 2009-09-28 : 15:17:13

If Im not mistaken once your do the unition you dont have a column for Miles2 anymore. You are trying to perform a column to column calculation but your end query only sees Miles once.

You may want to add a dummy Miles1 to your first select statment and make it 0 then add a dummy Miles 2 to the second one and make it a 0.

SELECT c.pickat, c.loadunload, c.deliverto, SUM(c.miles1 + c.miles2)
FROM(
SELECT a.pickat, a.loadunload, a.deliverto, b.miles as miles1, 0 as miles2
FROM actual_moves a Inner join
point2point As b on a.pickat = b.fromLocation AND a.loadunload = b.tolocation
UNION ALL
SELECT a.picakt, a.loadunload, a.deliverto, 0 as miles1, b.miles as miles2
FROM actual_moves a Inner join
point2point As b on a.loadunload = b.fromLocation AND a.deliverto = b.tolocation) c
GRoup by c.pickat, c.loadunload, c.deliverto, c.miles

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-28 : 15:18:51

Sample DATA

table1 
Pickat | loadunload | deliverto | depot
1          2           3           058
1          50          1           058
1          34          1           058
3          49          1           058

table2
MoveID|fromLocation|toLocation|miles
1        1          2           218        
2        1          50          596
3        1          34          150
4        3          49          136
5        2           3           52
6        50          1          596
7        34          1          150
8        49          1           12   

Expected Results
Pickat | loadunload | deliverto | totalmiles
1          2           3            270    --218 + 52
1          50          1           1192    --596 + 596
1          34          1            300    --150 + 150
3          49          1            148    --136 + 12

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 15:19:17

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-28 : 15:30:43

Good point..
I want to return the expected results from the 2 sample tables..

I would like to select pickat, loadunload, deliverto, and a sum of the miles between the pickat/loadunload move & the loadunload/deliveto move. total miles of trip

thanks

quote:
Originally posted by tkizer

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-28 : 16:47:35

[code]SELECT FromLocation, Tolocation, SUM(c.miles)
FROM(
SELECT FromLocation, Tolocation, miles
from point2point as a
where FromLocation = 'Am-Cell - Charleston SC' AND tolocation = 'Eka Chem. Inc. - Augusta GA'
UNION
SELECT FromLocation, Tolocation, miles
from point2point as b
where fromlocation = 'Eka Chem. Inc. - Augusta GA' AND toLocation = 'Wando Term. - Charleston SC')c
Group by fromlocation, tolocation[/code]

ok Ive narrowed it down, the above returns

[code]
Am-Cell - Charleston SC |Eka Chem. Inc. - Augusta GA | 139.300003051758
Eka Chem. Inc. - Augusta GA | Wando Term. - Charleston SC | 144.600006103516
[/code]

I need

[code]Am-Cell - Charleston SC | Eka Chem. Inc. - Augusta GA | Wando Term. - Charleston SC | 283.900009155273[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources