multi statement table valued function

Question

I have a table

     UserName  Question_ID  Answer
      Tom       Q001           D
      Wendy     Q009           A
      Eddy      Q089           C
      David     Q001           C
      Eve       Q001           D
      Paul      Q001           A
      Sam       Q001           B
      Tom       Q002           B
      Tom       Q003           C

I want to create multi statement tabled valued function.

Let Question_id as input, I want to create table shows question_id, answer, number of answers, and percentages of answers

For example (input: Question_id = Q001)

The output will be

   Question_ID Answer Total Percentage
    Q001          A      1       20
    Q001          B      1       20
    Q001          C      1       20
    Q001          D      2       40

I have created function

    create function [dbo].[QuestionFrequency]
    (
    @question_id varchar(10)    
    )

    Returns @frequency table (question_id varchar(10), answer varchar(10))
    As 
    begin

    insert @frequency (question_id, answer)
    select question_Id, Answer from questions where @question_id = Question_id
    return 
    end

it shows me the out put

Question_ID     Answer
Q001           D
Q001           C
Q001           D
Q001           A
Q001           B

What should i do to count the total and percentages?

roman · Accepted Answer · 2013-09-03 15:26:11Z

1

select
    q.Question_ID, q.Answer,
    count(*) as Total,
    count(*) * 100 / (select count(*) from questions as t where t.Question_ID = @question_id) as [Percentage]
from questions as q
where q.Question_ID= @question_id
group by q.Question_ID, q.Answer

edited Sep 3, 2013 at 15:26

answered Sep 3, 2013 at 15:13

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dongmin Won Over a year ago

Thanks, but still cannot get the percentage :)

Dongmin Won Over a year ago

i just changed to count() * 100 / (select count() from questions as t where.... it works :D thanks buddy

roman Over a year ago

@DongminWon yep, missed that

Sonam · Accepted Answer · 2013-09-03 15:24:51Z

0

SELECT
Question_ID,
Answer,
COUNT(*) TotaL_Answers,
(COUNT(*) / CONVERT(DECIMAL(2),(select COUNT(*) from TABLE_NAME where Question_ID= 'Q001')))*100 'Percent'
FROM TABLE_NAME
WHERE Question_ID= 'Q001'
GROUP BY Question_ID, Answer

answered Sep 3, 2013 at 15:24

Sonam

3,4861 gold badge14 silver badges25 bronze badges

2 Comments

roman Over a year ago

you have to add where clause to subquery

Sonam Over a year ago

Oh Yes, I missed that..Thanks.

Collectives™ on Stack Overflow

multi statement table valued function

I have created function

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I have created function

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related