D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Thursday, 6 October 2011

Using Count function on a uniqueidentifier field - SQL

SELECT COUNT(D.DOCUMENTID) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE

When running this query I encountered the following error:

Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.


   
To avoid this error when trying to Count a uniqueidetifier field you have to CAST it into a char :
 
SELECT COUNT(CAST(D.DOCUMENTID AS char(36))) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE