subquery question - 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)

subquery question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-22 : 07:40:41

Kenny writes "I have a database query I am writing which does several sub-queries that return the results as integers. I am trying to find the way to calculate the results back and add them as another returned field. The problem is it doesn't recognize these as valid columns. Here is an example of my sql query with the results


SELECT Members.Name, dbo.MemberClass.ClassName, dbo.MemberRanks.RankName, SUM(dbo.Raids.RaidValue) AS TotalEarned,
                          (SELECT     SUM(AdjustmentAmount)
                            FROM          dbo.MemberAdjustments
                            WHERE      (Members.ID = ID)) AS TotalAdjustment,
			  (SELECT SUM(ItemCost)
					FROM dbo.Items, dbo.MemberItems
					WHERE (MemberItems.MemberID = Members.ID) AND (MemberItems.ItemID = Items.ID)
				GROUP BY Members.Name) AS TotalSpent
				FROM dbo.MemberClass INNER JOIN
				                      dbo.Members AS Members ON dbo.MemberClass.ID = Members.Class INNER JOIN
				                      dbo.MemberRanks ON Members.Rank = dbo.MemberRanks.ID INNER JOIN
				                      dbo.MemberRaids ON Members.ID = dbo.MemberRaids.MemberID INNER JOIN
				                      dbo.Raids ON dbo.MemberRaids.RaidID = dbo.Raids.ID
				GROUP BY Members.ID, Members.Name, dbo.MemberRanks.RankName, dbo.MemberClass.ClassName

And the results


Name                 ClassName                                          RankName                                           TotalEarned TotalAdjustment TotalSpent  
-------------------- -------------------------------------------------- -------------------------------------------------- ----------- --------------- ----------- 
Sighted              Shaman                                             Guild Leader                                       23          <NULL>          <NULL>      
Draknor              Shaman                                             Officer                                            11          <NULL>          <NULL>      
Acumen               Shaman                                             Member                                             17          <NULL>          <NULL>      
Evaporated           Priest                                             Member                                             12          <NULL>          <NULL>      
Beckinsale           Priest                                             Member                                             12          <NULL>          <NULL>      
Mellop               Priest                                             Member                                             17          <NULL>          <NULL>

What I want to do is add another column which calculates (TotalEarned + TotalAdjustment) - TotalSpent and create another column called Current"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 07:44:08

Select *,Isnull(TotalEarned,0) + Isnull(TotalAdjustment,0) as Current from
(
SELECT Members.Name, dbo.MemberClass.ClassName, dbo.MemberRanks.RankName, SUM(dbo.Raids.RaidValue) AS TotalEarned, (SELECT SUM(AdjustmentAmount) FROM dbo.MemberAdjustments WHERE (Members.ID = ID)) AS TotalAdjustment, (SELECT SUM(ItemCost) FROM dbo.Items, dbo.MemberItems WHERE (MemberItems.MemberID = Members.ID) AND (MemberItems.ItemID = Items.ID) GROUP BY Members.Name) AS TotalSpent FROM dbo.MemberClass INNER JOIN dbo.Members AS Members ON dbo.MemberClass.ID = Members.Class INNER JOIN dbo.MemberRanks ON Members.Rank = dbo.MemberRanks.ID INNER JOIN dbo.MemberRaids ON Members.ID = dbo.MemberRaids.MemberID INNER JOIN dbo.Raids ON dbo.MemberRaids.RaidID = dbo.Raids.ID GROUP BY Members.ID, Members.Name, dbo.MemberRanks.RankName, dbo.MemberClass.ClassName) T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources