Count and Average across join - 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)

Count and Average across join

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-11-29 : 08:40:57

Hi Guru's,

I can't seem to get this query right:

SELECT 
  l.ID , l.LibraryName, ISNULL(SUM(fams),0) AS Familys,
  COUNT(UR1.Rank) AS Ranked,
  ISNULL(ROUND(AVG(UR1.Rank*1.0), 2), 0) AS AverageRank
FROM 
  Libraries l
LEFT JOIN 
  (SELECT LibraryID, count(*) AS fams FROM Products WHERE ObjectTypeID = 1 GROUP BY LibraryID) b ON l.ID = b.LibraryID
LEFT OUTER JOIN UserRankings UR1 ON l.ID = UR1.LibraryID
GROUP BY l.ID , l.LibraryName

It always returns Familys * Ranked. Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-29 : 11:19:50

Can you give the table structure and expected output too?