[RESOLVED] Group By All Columns except the image field

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

Code:

---

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

Code:

---

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

Code:

---

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

I got a Solution by casting the Field

Code:

---

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

---