adding sum to joined query - 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 2005 Forums

Transact-SQL (2005)

adding sum to joined query

Author

Topic

eflat
Starting Member

7 Posts

Posted - 2010-10-28 : 19:02:30

This is an extension of the last question I had. In this case I'm trying to join in the sum of grouping where I also added together values in each row. I thought something like this would work:


select a.id, b.id, c.id, d.cnt, e.sm
from tableA a
	left join tableB b on a.id = b.aid
	left join tableC c on b.id = c.bid
	left join (select aid,count(*) as cnt
			from tableD 
			group by aid) d
		on d.aid = a.aid
	left join (select aid, (sum(colA) + sum(colB)) as sm
			from tableE
			group by aid) e
		on e.aid = a.aid

That last join statement is apparently wrong. What should it be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:31:56

its syntatically correct. Dont understand why you think its wrong? are you not getting desired result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eflat
Starting Member

7 Posts

Posted - 2010-11-02 : 17:25:51

quote:
Originally posted by visakh16

its syntatically correct. Dont understand why you think its wrong? are you not getting desired result?

I was getting a wrong result and since you confirmed the syntax, I checked it out a bit more. And I figured out what was wrong.

In my example, there are alot of NULL values in colA and colB and summing those you get the result NULL. I changed it as follows:


select a.id, b.id, c.id, d.cnt, e.sm
from tableA a
	left join tableB b on a.id = b.aid
	left join tableC c on b.id = c.bid
	left join (select aid,count(*) as cnt
			from tableD 
			group by aid) d
		on d.aid = a.aid
	left join (select aid, (sum(coalesce(colA,0)) + sum(coalesce(colB,0))) as sm
			from tableE
			group by aid) e
		on e.aid = a.aid

Thanks again for helping.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources