problem with count, group by and ntext

I have one SELECT statement that needs to return one ntext field from
one table and count something from other table, problem is that all
fileds that are not in count have to be in group by and ntext can't be
in group by... i hope you understend what i want to say here :), so is
there any solution to this problem or what is the best workaraund you
would use?

example:
TABLE projects
project_id int
project_title nvarchar(50)
project_description ntext

TABLE users
user_id int
project_id int

SELECT projects.project_id, projects.project_title,
projects. project_description,
 count(users.*) AS number_od_users
FROM projects LEFT JOIN users ON projects.project_id=users.project_id
GROUP BY projects.project_id, projects.project_title,
projects. project_description;


this select is something that would work for me...if it wasn't for that
tiny problem with ntext field

Igor (jerosimic@gmail.com)  writes:
> I have one SELECT statement that needs to return one ntext field from
> one table and count something from other table, problem is that all
> fileds that are not in count have to be in group by and ntext can't be
> in group by... i hope you understend what i want to say here :), so is
> there any solution to this problem or what is the best workaraund you
> would use?
>
> example:
> TABLE projects
> project_id int
> project_title nvarchar(50)
> project_description ntext
>
> TABLE users
> user_id int
> project_id int
>
> SELECT projects.project_id, projects.project_title,
> projects. project_description,
 count(users.*) AS number_od_users
> FROM projects LEFT JOIN users ON projects.project_id=users.project_id
> GROUP BY projects.project_id, projects.project_title,
> projects. project_description;

>
> this select is something that would work for me...if it wasn't for that
> tiny problem with ntext field

SELECT p.project_id, p.project_title, p. project_description,

numbers_od_users = coalesce(u.cnt, 0)
FROM   projects p
LEFT   JOIN (SELECT project_id, cnt = COUNT(*)
FROM   users
GROUP  BY project_id) AS u ON p.project_id = u.project_id

The thing in parens is a derived table. Logically it is a temp table within
the table, but it never materialised, and the actual computation order may
be different. It is a very powerful tool to write SQL queries.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx