If you try to use the GROUP BY clause with the Text datatype it will throw an error telling you that you cannot use GROUP BY with Text DataType. There is a easy work around to this simply convert the Text Datatype to a varchar and the GROUP BY will work.

Here is a simple example:


-- Get all the post not depending if it has the thread or not 

SELECT p.PostID, p.Url, p.Title,COUNT(t.ThreadID),p.DateCreated,CONVERT(VARCHAR(8000),p.Description)
"Comments" FROM Posts p
LEFT OUTER JOIN Threads t ON p.PostID = t.PostID 
WHERE
p.Active = 1 
AND 
CONVERT(CHAR(6), p.DateCreated, 112) = CONVERT(CHAR(6),GETDATE(), 112) 
GROUP BY p.PostID,p.Url,p.Title,p.DateCreated,CONVERT(VARCHAR(8000),p.Description)





powered by IMHO