Multiple joins not yielding correct SUM

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)

Multiple joins not yielding correct SUM

Author

Topic

iglesia777
Starting Member

6 Posts

Posted - 2007-01-08 : 12:06:00

I am trying to get the SUM of Field1, Field2 by City_Name. Not much choice about the table structure as it is Census data.

tbl_Cities:
City_ID
City_Name

tbl_Blocks2000:
Block_ID
City_ID

tbl_Blocks1990:
Block_ID
City_ID

tbl_Info2000:
Block_ID
Field1

tbl_Info1990:
Block_ID
Field1

Desired result:
City_Name | SUM(tbl_Info2000.Field1) | SUM (tbl_Info1990.Field1)
City1 | 1000 | 950
City2 | 2000 | 1500

Here is my SQL:
SELECT
c.City_Name,
Sum(i2000.Field1),
Sum(i1990.Field1)
FROM
tbl_Cities c
INNER JOIN tbl_Blocks2000 b2000
ON c.City_ID = b2000.City_ID
INNER JOIN tbl_Info2000 i2000
ON b2000.Block_ID = i2000.Block_ID
INNER JOIN tbl_Blocks1990 b1990
ON c.City_ID = b1990.City_ID
INNER JOIN tbl_Info1990 i1990
ON b1990.Block_ID = i1990.Block_ID
GROUP BY c.City_Name

The SQL is putting it in the format I want, but the totals are way too high. Am I using the right kind of JOINs, missing parenthesis? Is what I'm trying to do possible? Let me know if I need to explain what blocks are or anything else.

Thanks a bunch,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 12:27:26

Give us some sample data for all tables to test our queries on.

Peter Larsson
Helsingborg, Sweden

iglesia777
Starting Member

6 Posts

Posted - 2007-01-08 : 15:13:11

Field names have changed slightly for an easier example (replaced cities with counties).

tbl_Counties
CountyID, CountyName
121,Muskegon County
123,Newaygo County
139,Ottawa County

tbl_Blocks1990
CountyID, BlockID
139,26139020100146
139,26139020100148
139,26139020100149
121,26121000100303
121,26121000100304
121,26123970100101
123,26123970100119
123,26123970100121
123,26123970100141

tbl_Blocks2000
CountyID, BlockID
139,26139020100126
139,26139020100128
139,26139020100129
121,26121000100207
121,26121000100208
121,26121000100209
123,26123970100126
123,26123970100127
123,26123970100113

tbl_Info1990
BlockID, Field1
26121000100303,5
26121000100304,46
26123970100101,23
26123970100119,4
26123970100121,16
26123970100141,82
26139020100146,7
26139020100148,55
26139020100149,23

tbl_Info2000
BlockID, Field1
26139020100126,24
26139020100128,14
26139020100129,35
26121000100207,5
26121000100208,65
26121000100209,24
26123970100126,6
26123970100127,70
26123970100113,18

Thanks again.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-08 : 15:45:07

You cannot join multiple transactional tables all in a single SELECT without first grouping them by a common key, unless you UNION them all together.

See:

http://weblogs.sqlteam.com/jeffs/archive/2006/06/19/10270.aspx

for general information for this, which hopefully will guide you in the right direction.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 16:52:16

Try this

SELECT		c.CountyID,
		c.CountyName,
		MAX(a.Blocks) AS '1990',
		MAX(b.Blocks) AS '2000'
FROM		tbl_Counties AS c
LEFT JOIN	(
			SELECT		b.CountyID,
					SUM(i.Field1) AS Blocks
			FROM		tbl_Blocks1990 AS b
			LEFT JOIN	tbl_Info1990 AS i ON i.BlockID = b.BlockID
			GROUP BY	b.CountyID
		) AS a ON a.CountyID = c.CountyID
LEFT JOIN	(
			SELECT		b.CountyID,
					SUM(i.Field1) AS Blocks
			FROM		tbl_Blocks2000 AS b
			LEFT JOIN	tbl_Info2000 AS i ON i.BlockID = b.BlockID
			GROUP BY	b.CountyID
		) AS b ON b.CountyID = c.CountyID
GROUP BY	c.CountyID,
		c.CountyName
ORDER BY	c.CountyID,
		c.CountyName

Peter Larsson
Helsingborg, Sweden

iglesia777
Starting Member

6 Posts

Posted - 2007-01-08 : 17:14:46

Hi Peter,

I get an error when I try your SQL. Looks good, but I found another way. It's still FAR from ideal, but it works.

Select
CountyID,
CountyName,
SUM(Sum2000) as Sum2000,
SUM(Sum1990) as Sum1990
FROM
(
SELECT
c.CountyID,
c.CountyName,
Sum(tbl_Info2000.Field1) AS Sum2000,
0 as Sum1990
FROM
tbl_Counties c
JOIN tbl_Blocks2000
ON w.CountyID = tbl_Blocks2000.CountyID
JOIN tbl_Info2000
ON tbl_Blocks2000.BlockID = tbl_Info2000.BlockID
GROUP BY c.CountyID, c.CountyName

UNION

SELECT
c.CountyID,
c.CountyName,
0 as Sum2000,
Sum(tbl_Info1990.Field1) AS Sum1990
FROM
tbl_Counties c
JOIN tbl_Blocks1990
ON c.CountyID = tbl_Blocks1990.CountyID
JOIN tbl_Info1990
ON tbl_Blocks1990.BlockID = tbl_Info1990.BlockID
GROUP BY c.CountyID, c.CountyName
) tmp
Group By CountyID, CountyName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:27:40

Which error did you get?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources