Unable to Group By text, ntext and image 1

[nickdaniels]

I have an SQL statement:

SELECT
contact_lookup.summary,
count(DISTINCT contact.vduid)
FROM
contact,
contact_lookup
WHERE
(contact_lookup.contact_key=contact.vduid)
GROUP BY
contact_lookup.summary

This generates the following message:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be used in a GROUP BY clause.278

If I remove the summary field, or just use it without aggregated fields like vduid I'm okay. I'm completely new to SQL Server, so far as I know this kind of statement would be allowed in other databases.

cheers, Nick

Nick
ndaniels@ventura-uk.com

If it's Data Warehousing or related then I want to know - but post it to the forum!

 

[fluteplr]

It is not the statement that is the problem, it is the datatype of the summary field. Apparently it is a text field (normaly used for large memos), since this type of field can hold up to 2 Gigabytes of data, sorting and grouping on them is not allowed. If this field does not actuallty hold large amounts of data you should probably change it to a char or varchar field which can be sorted or grouped.