Get No. of times a colum of a table appears in another table.

Question

Get No. of times a colum of a table appears in another table.

Reddy-309776

Mr or Mrs. 500

Points: 503
More actions
March 13, 2006 at 7:53 am

#113621

Hi i have two tables.
CREATE TABLE [tblSurvey2_Questions] (
[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[SurveyID] [int] NULL ,
[QuestionNum] [int] NULL ,
[Question] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AnswerType] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuizFlag] [bit] NULL ,
[RequiredFlag] [bit] NULL ,
[Choice1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice6] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice7] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice8] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice9] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice10] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice11] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice12] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice13] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice14] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice15] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice16] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice17] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice18] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice19] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Choice20] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CorrectChoice] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answers1] [int] NULL ,
[Answers2] [int] NULL ,
[Answers3] [int] NULL ,
[Answers4] [int] NULL ,
[Answers5] [int] NULL ,
[Answers6] [int] NULL ,
[Answers7] [int] NULL ,
[Answers8] [int] NULL ,
[Answers9] [int] NULL ,
[Answers10] [int] NULL ,
[Answers_Correct] [int] NULL ,
[TimesAnswered] [int] NULL ,
CONSTRAINT [PK_tblSurvey2_Questions] PRIMARY KEY CLUSTERED
(
[QuestionID]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblSurvey2_Answers] (
[UserAnswerID] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionID] [int] NULL ,
[SurveyID] [int] NULL ,
[UserID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[ModifyDate] [datetime] NULL ,
CONSTRAINT [PK_tblSurvey2_UserAnswers] PRIMARY KEY CLUSTERED
(
[UserAnswerID]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO
here, if the question is of type checkbox/radio i have to find how manytimes each choice appears in the answers table. I did this using multiple calls from vb .net but it takes forever. The approach i followed was:
1) first find all the rows of type ('radio', 'checkbox') in tblSurvey2_questions
2) in a Loop: find each time its corresponding choice appears in the tblsurvey2_answers table
3) Calculate % of times it appears. END LOOP
This takes forever, and the request times out for like 15 rows itself.
So i tired using a sp, but i got stuck there. I got till finding the column name, but after that, im not able to go how to proceed. sp till where i got is:
declare @sql1 varchar(255)
declare @i int
declare @checkvalues varchar(255)
declare @checkvalues1 varchar(255)
set @i=1
set @checkvalues = 'as'
--print @checkvalues
set @checkvalues1 = 'asd'
--print @checkvalues1
while len(@checkvalues1) >0 -- (@i < 20)
begin
select @checkvalues = 'choice'+convert(varchar(1), @i) from tblSurvey2_questions where questionID = 7
--set @sql1 =' select ' + @checkvalues1+' = ltrim(rtrim('+@checkvalues+')) from tblSurvey2_questions where questionID = 7'
set @sql1 ='select choice'+convert(varchar(1), @i)+' from tblSurvey2_questions where questionID = 7'
--select @checkvalues from tblSurvey2_questions where questionID = 7
exec(@sql1)
print @checkvalues
--print @checkvalues1
set @i = @i +1
if @i =10
break
else
continue
end
Any help is appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the DDL helps a lot, in this case i think i'd need row values to extrapolate this correctly.

i think this group by statement would give you the question as well as the breakdown of answers per question; test it and see if it even aproaches what you were after:

select QuestionID,

Answer,

count(Answer) as CountOfAnswer,

count(QuestionID) as CountOfQuestions,

convert(money,count(Answer) / count(QuestionID)) as PercentOfQuestions

from tblSurvey2_Answers

where QuestionID in(

select QuestionID

from tblSurvey2_questions where QuestionID

in(select QuestionID where AnswerType in ('radio', 'checkbox') )

group by QuestionID,Answer

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 2

Lowell:

select QuestionID, Answer, count(Answer) as CountOfAnswer, count(QuestionID) as CountOfQuestions,

convert(money,count(Answer)/count(QuestionID)) as PercentOfQuestions

from tblSurvey2_Answers where QuestionID in(select QuestionID from tblSurvey2_questions where QuestionID

in(select QuestionID where AnswerType in ('radio', 'checkbox')) )group by QuestionID, Answer order by questionID

Works, but the %'s are not right. Im trying to correct it. your solution is very elegant. I tried all kind of stuff, This is great.

If you can think why the %'s are not coming right, please let me know.

Thanks again Lowell.

noeld SSC Guru Points: 96590 More actions · Answer 3

I can guess there is integer division involved.

Try:

...

convert(money,count(Answer)*1.0/count(QuestionID)) as PercentOfQuestions

...

Cheers,

* Noel

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 4

Noel that doesn't help.

Is there a way to get the choices and their corresponding count?

thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 5

I would normalize it first and than do the math.

_____________
Code for TallyGenerator