Query with COUNT - 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)

Query with COUNT

Author

Topic

maxovn
Starting Member

3 Posts

Posted - 2008-06-12 : 13:50:08

I have 3 tables with 1 to many relationship as discribed below

Table A
CompanyId CompanyName
1 ABC
2 BCD
3 XYZ
4 TTT

Table B
CompanyId LocationId
1 a
1 b
2 c
3 d

Table C
LocationId DepartmentId
a it
a hr
a el
b gh
c tr

I want to write a query scan the Table A to display row in table A and # of Location and Department belong to the Company, like this:

CompanyId CompanyName # of Location # of Department
1 ABC 2 4
2 BCD 1 1
3 XYZ 1 0
4 TTT 0 0

Please help me
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 13:54:23

[code]SELECT a.CompanyId,a.CompanyName,
COUNT(DISTINCT b.LocationId) AS [# of Location],
COUNT(DepartmentId) AS [# of Department]
FROM TableA a
INNER JOIN TableB b
ON b.CompantId=a.CompanyId
INNER JOIN TableC c
ON c.LocationId=b.LocationId
GROUP BY a.CompanyId,a.CompanyName[/code]

maxovn
Starting Member

3 Posts

Posted - 2008-06-12 : 15:08:28

Good Thanks

But result missing 2 rows

Here is the result when runing that script

-----------------------
1 ABC 2 4
2 BCD 1 1
-----------------------

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 15:41:10

I guess you require to use left outer join...


SELECT a.CompanyId,a.CompanyName,
COUNT(DISTINCT b.LocationId) AS [# of Location],
COUNT(DepartmentId) AS [# of Department]
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.CompantId=a.CompanyId
LEFT OUTER TableC c
ON c.LocationId=b.LocationId
GROUP BY a.CompanyId,a.CompanyName

Chirag

http://www.chirikworld.com

maxovn
Starting Member

3 Posts

Posted - 2008-06-12 : 16:04:06

Yes, you are right.

To get all rows, we have to use left outer join.

Thanks ALL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources