By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

problem with count, group by and ntext

P: n/a
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

Apr 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Igor (je*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 7 '06 #2

P: n/a
I wrote it like this:
SELECT projects.project_id, projects.project_title,
projects.project_description, (SELECT count(*) FROM users WHERE
projects.project_id=users.project_id) AS number_od_users
FROM projects;

what does "coalesce" do?

Apr 10 '06 #3

P: n/a
Igor (je*******@gmail.com) writes:
I wrote it like this:
SELECT projects.project_id, projects.project_title,
projects.project_description, (SELECT count(*) FROM users WHERE
projects.project_id=users.project_id) AS number_od_users
FROM projects;
This is most likely slower than the version I suggested. Sub-selects in the
SELECT list often gives poor performance.
what does "coalesce" do?


coalesce takes list of values, and returns the first non-NULL value in the
list, or NULL if all values are NULL.

coalesce() is described in Books Online, if you want to read more.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 10 '06 #4

P: n/a
OK, thanks

Apr 14 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.