join and a count? 1

[LesStockton]

I've got 2 tables; basically, topics and messages.
How do I list the topics and then the count for each topic?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[udForumTopics]
@CompanyID int
AS
SELECT
T_ForumTopic.ForumTopicID AS "ForumTopicID",
T_ForumTopic.TopicTitle AS "TopicTitle",
T_ForumTopic.ActiveInd AS "TopicActive",
T_ForumTopic.OwnerCompany AS "ForumTopicOwnerCompany",
T_ForumTopic.CreateUser AS "ForumTopicCreateUser",
T_ForumTopic.UpdateUser AS "ForumTopicUpdateUser",
T_ForumTopic.CreateDate AS "ForumTopicCreateDate",
T_ForumTopic.UpdateDate AS "ForumTopicUpdateDate",
Count(ForumTopicMessageID) AS MessageCount
FROM [T_ForumTopic]
INNER JOIN [T_ForumTopicMessage] ON [T_ForumTopicMessage].ForumTopicID = [T_ForumTopic].ForumTopicID
WHERE T_ForumTopic.OwnerCompany = @CompanyID
GROUP By T_ForumTopic.ForumTopicID

This doesn't seem to work. I thought I could do a count on ForumtopicMessageID from the second table, but SQL Server Management Studio says that
"Column 'T_ForumTopic.TopicTitle' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

 

[gmmastros]

Maybe this...

ALTER PROCEDURE [dbo].[udForumTopics]
    @CompanyID int
AS
SELECT 
    T_ForumTopic.ForumTopicID AS "ForumTopicID",
    T_ForumTopic.TopicTitle AS "TopicTitle",
    T_ForumTopic.ActiveInd AS "TopicActive",
    T_ForumTopic.OwnerCompany AS "ForumTopicOwnerCompany",
    T_ForumTopic.CreateUser AS "ForumTopicCreateUser",
    T_ForumTopic.UpdateUser AS "ForumTopicUpdateUser",
    T_ForumTopic.CreateDate AS "ForumTopicCreateDate",
    T_ForumTopic.UpdateDate AS "ForumTopicUpdateDate",
    A.MessageCount AS MessageCount
FROM [T_ForumTopic]
     Left Join 
       (
		Select [T_ForumTopicMessage].ForumTopicID,
		       Count(1) As MessageCount
		From   [T_ForumTopicMessage]
       ) As A On A.ForumTopicId = [T_ForumTopic].ForumTopicId
WHERE T_ForumTopic.OwnerCompany = @CompanyID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom