|
Home > Archive > Microsoft SQL Server forum > April 2006 > problem with count, group by and ntext
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Erland Sommarskog 2006-04-07, 7:35 am |
| 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
|
|
|
|
|