adding count 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 count to joined query

Author

Topic

eflat
Starting Member

7 Posts

Posted - 2010-10-27 : 13:17:00

I have a query with some left joins:

select a.id, b.id, c.id
from tableA a
	left join tableB b on a.id = b.aid
	left join tableC c on b.id = c.bid

Now I want to include tableD, where tableA links to many records in tableD, and I just want the count of how many added to my select statement. So the select would look something like:

select a.id, b.id, c.id, count(d.aid)

How do I get there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 13:53:32

[code]
select a.id, b.id, c.id,d.cnt
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
[/code]

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

eflat
Starting Member

7 Posts

Posted - 2010-10-27 : 14:24:50

Yes, thank you! That certainly opens up many more possibilities!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:29:48

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources