Adding multiple columns from multiple 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

General SQL Server Forums

New to SQL Server Programming

Adding multiple columns from multiple tables

Author

Topic

tonzo1883
Starting Member

12 Posts

Posted - 2008-12-31 : 06:10:40

Hi,

I am having trouble adding values from two columns on two different tables. I want to add together the following two queries:

SELECT     TOP (10) SUM(dbo.Goals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.Goals INNER JOIN
                      dbo.Players ON dbo.Goals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName

Which returns:

Goals PlayerName
21 Adam Jones
132 Alan Shearer
4 Jason Sardo

and this query:

SELECT     TOP (10) SUM(dbo.ArchiveGoals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.ArchiveGoals INNER JOIN
                      dbo.Players ON dbo.ArchiveGoals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName

Which returns:

Goals PlayerName
555 Adam Jones

I though this would do the trick:

SELECT     TOP (10) SUM(Goals.Goals)+ SUM(ArchiveGoals.Goals) AS Goals, Players.PlayerName
FROM         Goals INNER JOIN
                      Players ON Goals.PlayerID = Players.PlayerID INNER JOIN
                      ArchiveGoals ON Players.PlayerID = ArchiveGoals.PlayerID
GROUP BY Players.PlayerName

But all I get is:

Goals PlayerName
1131 Adam Jones

The query does not seem to have been grouped by the PlayerName.

Any help gratefully received!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 06:41:41

seems like what you need is this


SELECT m1.PlayerName,m1.Goals + COALESCE(m2.Goals,0) AS Goals
FROM
(
SELECT     TOP (10) SUM(dbo.Goals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.Goals INNER JOIN
                      dbo.Players ON dbo.Goals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName
)m1
LEFT JOIN 
(
SELECT     TOP (10) SUM(dbo.ArchiveGoals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.ArchiveGoals INNER JOIN
                      dbo.Players ON dbo.ArchiveGoals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName
)m2
ON m1.PlayerName=m2.PlayerName

tonzo1883
Starting Member

12 Posts

Posted - 2008-12-31 : 06:48:00

Great Thanks.

I am not sure how but that did the trick!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 06:51:04

thats because you dont have all players details coming from both the queries. thats why i took left join with the two queries rather than trying to merge them together.

tonzo1883
Starting Member

12 Posts

Posted - 2008-12-31 : 07:10:31

Ah, I have noticed a slight problem with the solution.

If a player has goals in the ArchiveGoals table but not in the Goals table the goals from the ArchivedGoals table are not displayed in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 07:43:23

quote:
Originally posted by tonzo1883

Ah, I have noticed a slight problem with the solution.

If a player has goals in the ArchiveGoals table but not in the Goals table the goals from the ArchivedGoals table are not displayed in the query.

then use this modified query


SELECT COALESCE(m1.PlayerName,m2.PlayerName) AS PlayerName,COALESCE(m1.Goals,0) + COALESCE(m2.Goals,0) AS Goals
FROM
(
SELECT     TOP (10) SUM(dbo.Goals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.Goals INNER JOIN
                      dbo.Players ON dbo.Goals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName
)m1
FULL OUTER JOIN 
(
SELECT     TOP (10) SUM(dbo.ArchiveGoals.Goals) AS Goals, dbo.Players.PlayerName
FROM         dbo.ArchiveGoals INNER JOIN
                      dbo.Players ON dbo.ArchiveGoals.PlayerID = dbo.Players.PlayerID
GROUP BY dbo.Players.PlayerName
)m2
ON m1.PlayerName=m2.PlayerName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources