Select with count from 2 joined 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select with count from 2 joined tables

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-09-06 : 07:24:40

Hi Guru's,

May be being a bit stupid here but I can't get these counts to work properly.

I have:

SELECT DISTINCT Companies.ID, Companies.Name, COUNT(Products.CompanyID) as productRecords, COUNT(Types.CompanyID) AS typeRecords 
FROM  Companies INNER JOIN
      Countries ON Companies.CountryID = Countries.CountryId INNER JOIN
      CompanyProperties ON Companies.CompanyID = CompanyProperties.CompanyID RIGHT OUTER JOIN
      Products ON Companies.CompanyID = Products.CompanyID RIGHT OUTER JOIN
	  Types ON Companies.CompanyID = Types.CompanyID 
WHERE Countries.CountryID = 1 AND CompanyProperties.Shared = 1
GROUP BY Companies.CompanyID, Companies.Name
ORDER BY Companies.Name

Do I have to do a nested union query? Basically I am trying to get the SUM of the 2 counts.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-06 : 07:29:32

See article http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources