Problems with Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems with Group By

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2009-02-11 : 16:49:25

Hi,

I have the following table:

member_id interview_seq question_id text_response
1234 500001 502103 Various values

I want to pull the member_id, interview_seq, and the text_response value when the question_id = 502103. This is the query I came up with:

select member_id, interview_seq,
CASE
WHEN question_id = 502103 THEN text_response
ELSE 0
END as 'Chronicity HB1'
from ccmsdba.member_interview_resp
where member_id = '499949693 01'
group by member_id, interview_seq, question_id, convert(varchar(50),text_response)

I am getting the error: Column 'ccmsdba.member_interview_resp.text_response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have also tried the query without the convert function and get the following:
Msg 257, Level 16, State 51, Line 1
Implicit conversion from data type text to int is not allowed. Use the CONVERT function to run this query.
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Any assistance is appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 16:51:38

WHEN question_id = 502103 THEN text_response

should be

WHEN question_id = 502103 THEN convert(varchar(50),text_response)

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 16:53:01

Also should be

ELSE '0'

lbeese
Starting Member

24 Posts

Posted - 2009-02-12 : 08:33:42

Thanks for the response. I tried the following this morning:

select member_id, interview_seq,
CASE
WHEN question_id = 502103 THEN convert (varchar(50), text_response)
ELSE '0'
END as 'Chronicity HB1'
from ccmsdba.member_interview_resp
where member_id = '499949693 01'
group by member_id, interview_seq, question_id, text_response

and am still getting: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 08:36:41

[code]select member_id,
interview_seq,
CASE
WHEN question_id = 502103 THEN SUBSTRING(text_response, 1, 50)
ELSE '0'
END AS [Chronicity HB1]
from ccmsdba.member_interview_resp
where member_id = '499949693 01'
group by member_id,
interview_seq,
question_id,
SUBSTRING(text_response, 1, 50)[/code]The parts in red need to be the same!

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 08:37:30

[code]Select member_id, interview_seq,
CASE
WHEN question_id = 502103 THEN convert (varchar(50), text_response)
ELSE '0'
END as 'Chronicity HB1'
from ccmsdba.member_interview_resp
where member_id = '499949693 01'
group by member_id, interview_seq, question_id,convert (varchar(50), text_response)[/code]

lbeese
Starting Member

24 Posts

Posted - 2009-02-12 : 08:55:40

I've tried both of the suggestions: using SUBSTRING and CONVERT and am still receiving:
Column 'ccmsdba.member_interview_resp.text_response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 09:06:07

lbeese
Starting Member

24 Posts

Posted - 2009-02-12 : 09:23:43

That did it! I didn't need the group by. Thanks for everyone's help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 09:27:49

[code]select member_id,
interview_seq,
COALESCE(SUBSTRING(text_response, 1, 50), '0') AS [Chronicity HB1]
from ccmsdba.member_interview_resp
where question_id = 502103[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources