Group By All Columns except the image field

Vimalsoft Pty Ltd

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

GROUP BY
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
A.ACCOUNTID,
PHOTO

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.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Vimalsoft Pty Ltd

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Lost User

Alternatively, you can use this. I'm assuming that the column PHOTO is in the USER table. I see that some of the columns in the query have not been prefixed with an alias, it is always a better practise to prefix all column names with alias, it improves performance too...

SELECT
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
(SELECT PHOTO FROM USER UP WHERE U.USERID = UP.USERID) AS 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

GROUP BY
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
A.ACCOUNTID