[RESOLVED] Group By All Columns except the image field

[vuyiswamb]

Good evening all

i have an image in my select fields, and i know that the Group by clause cant take the Image field, i have a query that looks like this

SELECT      
K.KIDID,      
K.STUDENTNUMBER,      
KIDNAME,      
KIDLASTNAME ,      
SC.SCHOOLNAME ,      
KIDGRADE ,      
KIDCLASS ,      
SC.SCHOOLID,  
k.TEMP_BARCODE,
PHOTO,
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE]  
FROM       
KIDS_DETAILS K      
INNER JOIN SCHOOL SC       
ON K.SCHOOLID = SC.SCHOOLID      
INNER JOIN PARENTKID PK       
ON PK.KIDID = K.KIDID
INNER JOIN USERS U 
ON U.USERID  = K.USERID 
INNER JOIN ACCOUNTS A 
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID  = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID

now as you can see there is a part where i sum , but now i have a PHOTO column, how can i remove this with out getting the common exception that will tell me that Photo is not in

Column 'KIDSPHOTO.PHOTO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and if i include it i will get the group by Clause limitation of the Type Text , image

Msg 306, Level 16, State 2, Line 40
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Thanks

[vuyiswamb]

I got a Solution by casting the Field

Select
KIDID,  
STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SCHOOLID, 
TEMP_BARCODE,
Cast(PHOTO as Image) as [PHOTO],
ACCOUNTID,
Balance
From
(
SELECT  
K.KIDID,  
K.STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SC.SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SC.SCHOOLID, 
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary) Photo, ---- Some Changes
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE] 
FROM  
KIDS_DETAILS K  
INNER JOIN SCHOOL SC  
ON K.SCHOOLID = SC.SCHOOLID  
INNER JOIN PARENTKID PK  
ON PK.KIDID = K.KIDID
INNER JOIN USERS U
ON U.USERID = K.USERID
INNER JOIN ACCOUNTS A
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
Group by 
K.KIDID,  
K.STUDENTNUMBER,  
KIDNAME,  
KIDLASTNAME ,  
SC.SCHOOLNAME ,  
KIDGRADE ,  
KIDCLASS ,  
SC.SCHOOLID, 
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary),
A.ACCOUNTID
) X