Professional Web Applications Themes

Is this possible - Percentages, etc from query. - ASP Database

I have a table called responses: RID int identity, Question1 int, Question2 int, Question3 int The user has the choice of answering questions with a value of 1 = good, 2 = OK, 3 = bad. OK, I ran this query to get the number of good, Ok, and bad results: ---------Query Start -------------- SELECT Count(Question1) AS [Question #1], Response = CASE(Question1) WHEN 1 THEN 'Good' WHEN 2 THEN 'OK' WHEN 3 THEN 'Bad' ELSE 'DID NOT ANSWER' End FROM MyQuestions GROUP BY Question1 ORDER BY Question1 ---------Query End ----------------- It gives me this output: 5 DID NOT ANSWER 62 ...

  1. #1

    Default Is this possible - Percentages, etc from query.

    I have a table called responses:
    RID int identity,
    Question1 int,
    Question2 int,
    Question3 int

    The user has the choice of answering questions with a
    value of 1 = good, 2 = OK, 3 = bad.

    OK, I ran this query to get the number of good, Ok, and
    bad results:
    ---------Query Start --------------
    SELECT
    Count(Question1) AS [Question #1],
    Response = CASE(Question1)
    WHEN 1 THEN 'Good'
    WHEN 2 THEN 'OK'
    WHEN 3 THEN 'Bad'
    ELSE 'DID NOT ANSWER'
    End

    FROM MyQuestions GROUP BY Question1 ORDER BY Question1
    ---------Query End -----------------

    It gives me this output:

    5 DID NOT ANSWER
    62 Good
    43 OK
    24 Bad

    This is great, it is progress, but now I want to add
    another column that will give me the percentage of each
    answer type for for example, let's assume there were 100
    respondents, then I want another column that will have
    this info:

    5 DID NOT ANSWER .05 or 5%
    62 Good .62 or 62%
    43 OK .43 or 43%
    24 Bad .24 or 24%

    My question(s). Can you do this in one query? I tried to
    grab the count then put it in a variable recCount, but
    when I tried to set up the equation, it gave me an error
    stating that I cannot do equations and get data in same
    select. What way(s) could I go about generation that
    query, if it is indeed possible?

    Shawn




    Shawn Guest

  2. #2

    Default Re: Is this possible - Percentages, etc from query.

    You can do that in a query, but I think it would be easier to do it in ASP
    once you have all the results.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Shawn" <microsoft.com> wrote in message
    news:1a3401c4a718$2a157700$gbl... 


    Aaron Guest

  3. #3

    Default Re: Is this possible - Percentages, etc from query.

    Just out of curiosity for my own understanding, what are
    the limitations? Is it that you would have to have 2
    different select statements or something similar?

    I agree it is easy in ASP, thought I try to do it all in
    SQL for kicks.

    Thanks Aaron.



     
    easier to do it in ASP 
    message [/ref]
    to 
    >
    >
    >.
    >[/ref]
    Shawn Guest

  4. #4

    Default Re: Is this possible - Percentages, etc from query.

    Shawn wrote: 


    Yes, it's possible to do it in one query. However, I would do it like this
    (it will be more efficient):

    DECLARE recCount int
    SET recCount=(SELECT Count(Question1) FROM MyQuestions);
    SELECT
    Count(Question1) AS [Question #1],
    Response = CASE(Question1)
    WHEN 1 THEN 'Good'
    WHEN 2 THEN 'OK'
    WHEN 3 THEN 'Bad'
    ELSE 'DID NOT ANSWER'
    End,
    100*Count(Question1)/recCount As [Percent]
    FROM MyQuestions GROUP BY Question1
    ORDER BY Question1

    Don't you want to order by Count(Question1)?


    Even though this can be sent as a batch using dynamic sql, I would
    encapsulate it in a stored procedure.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  5. #5

    Default Re: Is this possible - Percentages, etc from query.

    This is not an optimal solution because you have to run an aggregate
    subquery for every grouping in the result (ugh).



    CREATE TABLE Responses
    (
    RID INT IDENTITY,
    Question1 INT,
    Question2 INT,
    Question3 INT
    )
    GO

    SET NOCOUNT ON
    INSERT Responses(Question1, Question2, Question3)
    SELECT 1,2,3
    INSERT Responses(Question1, Question2, Question3)
    SELECT 3,2,1
    INSERT Responses(Question1, Question2, Question3)
    SELECT 2,3,1
    INSERT Responses(Question1, Question2, Question3)
    SELECT 2,3,1
    INSERT Responses(Question1, Question2, Question3)
    SELECT NULL,1,2
    INSERT Responses(Question1, Question2, Question3)
    SELECT NULL,NULL,NULL
    INSERT Responses(Question1, Question2, Question3)
    SELECT NULL,3,NULL
    GO

    SELECT
    [#] = COUNT(*),
    [%] = CONVERT
    (
    DECIMAL(4,2),
    100.0*COUNT(*) /
    (SELECT COUNT(*) FROM Responses)
    ),
    Answer = CASE(Question1)
    WHEN 1 THEN 'Good'
    WHEN 2 THEN 'OK'
    WHEN 3 THEN 'Bad'
    ELSE 'DID NOT ANSWER'
    END
    FROM
    Responses r
    GROUP BY
    Question1
    ORDER BY
    Question1

    GO

    DROP TABLE Responses


    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Shawn" <microsoft.com> wrote in message
    news:1a7001c4a71b$99ed2e30$gbl... 
    > easier to do it in ASP 
    > message [/ref]
    > to 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Aaron Guest

Similar Threads

  1. percentages in mx
    By Moggie webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: January 13th, 03:10 PM
  2. onstat -P buffer percentages
    By Glen Johnson in forum Informix
    Replies: 3
    Last Post: November 17th, 01:25 PM
  3. About percentages
    By Ed in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 18th, 05:38 PM
  4. Percentages
    By Gianna in forum Microsoft Access
    Replies: 1
    Last Post: July 1st, 08:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139