Group By All Columns except the image field [Resolved]

Posted by Vuyiswamb under Sql Server on 5/21/2011 | Points: 10 | Views : 6148 | Status : [Member] [MVP] [Administrator] | Replies : 1
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

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Vuyiswamb on: 5/21/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down

Resolved
i got a Solution


Select

K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
Cast(PHOTO as Image),
A.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


I needed to cast it

Thank you for posting at Dotnetfunda
[Administrator]

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response