[thelist] Displaying survey results

[rudy]

> You can still do the simple query for the totals,
> and a different query for individual info. That's
> probably some kind of horrid database no-no....

matthew, there are two ways to look at this (using separate queries) --
does it work, i.e. get the right answer, and does it fly

when you have two queries, chances are they'll get you the right answer,
even if this isn't always the most efficient way to access the database

i'd rather visit a web site that gives the right answer, even if it's slow,
than a site that gives me a bad answer really fast

okay, let me see if i can add some to the survey query problem

minh lee, it sounds like you have one column per question, with one row for
all of the responses of one user

the good news is, you do *not* have to query for each individual answer a,
b, c, d,  --  all you have to do is use GROUP BY

assuming the answers to question 13 are entered in a column called
column13, your query would look like this --

   select 'question 13'
       , column13, count(*)
     from yourTable
         group by column13

this should give you a result set that looks something like this --

    question 13    a     432
    question 13    b     125
    question 13    c       97
    question 13    d     177

when you group on a column, you can use aggregate functions like count(*),
which gets the count of all occurrences of each value of the column (not
sure if i'm explaining this terribly well)

anyhow, you will also want to have a count of all the rows, so you'd use
the count(*) function on the entire table like this --

   select count(*)
     from yourTable

[aside: let's not talk about nulls just yet]

you can combine this with the previous query using the UNION operator, but
you need to use a little wee trick

   select '  ', ' ', count(*)
     from yourTable
  union all
   select 'question 13'
       , column13, count(*)
     from yourTable
         group by column13
  order by 1,2

the wee little trick is there are blanks in the first two columns of the
result set of the first query -- this is necessary because all result sets
in the UNION have to have the same number of columns, and they have to be
the same types (character, numeric, etc.)

also notice the blanks will sort first

anyhow, the beauty of UNION is that you get to pass just the one query to
the database, even though it's really more than one query (the proper
terminology is a fullselect consisting of several subselects)

here's the UNIONed result set, sorted, with "~" used instead of a blank
(just for clarity) --

    ~                      ~     831
    question 13    a     432
    question 13    b     125
    question 13    c       97
    question 13    d     177

now depending on your scripting language (in cold fusion, you'd use the
GROUP= parameter on the CFOUTPUT tag), you just loop through the result
set, and calculate the percentage for each of the answers a, b, c, d, based
on what the total count is, which you got first and stored in a variable

   432/831 = 51%
   125/831 = 15%
   etc.

as for how to show this information graphically, deke gave you a great
method, and all you have to do is apply the percentage to the lengths of
the gifs (e.g. if the longest possible gif was going to be 200 pixels, then
51% would be 102 pixels, 15% would be 30, etc.)

finally, if you want to get the results for all the questions in one
database query, just union them all together --

   select '  ', ' ', count(*)   from yourTable
  union all
   select 'question 01' , column1, count(*)  from yourTable
         group by column1
  union all
   select 'question 02' , column2, count(*)  from yourTable
         group by column2
  union all
   select 'question 03' , column3, count(*)  from yourTable
         group by column3
  /* etc */
  order by 1,2


please let me know if any of this doesn't make sense


rudy.ca