Database Question

I am trying to generate a stats page for my survey database.

I have 4 questions.

Question1 has choices of 1,2,3
and Question2 has choices of yes, no
and Question3 has choices of yes, no, maybe
and Question4 has choices of bad, medium, good

I want to generate a page with percentages. for example,

Question 1:
50% picked 1
25% picked 2
25% picked 3

Question 2:
65% picked yes
35% picked no

Question 3:
33% picked yes
33% picked no
33% picked maybe

Question 4:
70% picked bad
20% picked medium
10% picked good

I have database in Informix server.

Can anyone help me? thanks.
sunghkimAsked:
Who is Participating?
 
tgoetzeConnect With a Mentor Commented:
# You can use fetchall_arrayref() but you will still need to
#  walk the data to add up frequencies.
# Here is how to do it.

#Assuming you can get connected to the database with Perl
$sqlquery="select Q1, Q2, Q3, Q4 from RESULTS_TABLE";

$csr=$lda->prepare($sqlquery) || warn $DBI::errstr;

$csr->execute();

$results_ref = $csr->fetchall_arrayref;

#load up the data into a hash
foreach $ref (@{$results_ref}) {
  $results{'Q1'}{$ref->[0]}++;
  $results{'Q2'}{$ref->[1]}++;
  $results{'Q3'}{$ref->[2]}++;
  $results{'Q4'}{$ref->[3]}++;
  $total_answers_count++;
}
# now you can close the database connection and do the computations

print "Total number of answers was: $total_answers_count\n";
foreach $question qw('Q1', 'Q2', 'Q3', 'Q4') {
    print "\nResults for $question:\n";
    foreach $result (keys %{$results{$question}}) {
        print "    $result was selected $results{$question}{$result} times.\n";
    }
}
0
 
shlomoyCommented:
issue a select statement to your database asking how many answers you have totally (for question #1, for example).
then issue a select statement for each answer for question 1, and divide the result by the number of total answers you got for question #1. The resulting number (multiplied by 100) is the % of people answering that question with that answer.

0
 
maneshrCommented:
before implementing a solution there are certain prerequistes.

* you need to have some way to connect to your informix database, using the PERL DBI module for eg.
* there must be a DB user setup who can issue DDL and DML statements from within the script.


Assuming all that is done, writing the actual script is the easier part.
For this one would need the DB table structure & a few sample set of values.

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
sunghkimAuthor Commented:
I already have DBI and I would like to use one select to get all the information.

What i need is the sample of a
Select statement where I can get all the info I need.

thanks.
0
 
christopher sagayamCommented:
In informix Im not sure whether the following SQL statements are valid but it is valid in Mysql

get the total number of answers for question 1 ..

Note QUESTION is fieldname

1) SELECT COUNT(*) FROM tablename WHERE QUESTION = 1

ANSWER is also a field name

2) SELECT COUNT(*) FROM tablename WHERE QUESTION =1 AND ANSWER = 1 ;


3) use the perl script to find out the percentage of each answer to a question

by using X/Y * 100 where X is the result of step 2 and Y is the result of step1





0
 
maneshrCommented:
here is a small sample script that i created.

this script connects to my DB2 DB and does a simple query.

all that you need to do is change it  and put in your DB name, username, password, tablename & columns and you are ready to go...

============================sunghkim.pl
#!/usr/local/bin/perl

use DBI;

## DBI->connect($data_source, $username, $auth);
$lda = DBI->connect("DBI:DB2:DBinstance",
"user","userpassword") || die "Could not access database: $DBI::errstr";

##  this is the actual query!!
$sqlquery="select col1, col2 from web_users_log";

##Prepare a single statement for execution by the database
##engine and return a reference to a statement handle
##object which can be used to get attributes of the
##statement and invoke the the execute entry elsewhere in
##this script.
$csr=$lda->prepare($sqlquery) || warn $DBI::errstr;

##Perform whatever processing is necessary to execute the
##prepared statement.
$csr->execute();

## Get the results!!
while(($col1,$col2)=($csr->fetchrow())){
  print $col1,"\t",$col2,"\n";
}

0
 
sunghkimAuthor Commented:
chris18,

your answer only works for 1 question at a time.
so for 4 questions, i need to write 4 select statements.

I want to write just one select statement to dump it into the reference array to work with it later.

0
 
tgoetzeCommented:
The select statement you need (if it is even possible to write) will completely depend on how the data is stored in your database. If you want someone to help with select statement, you will need to explain how the data lives in the database.

Good information would be the table names, column names, and any insert statements that were used to load the data. Also a description of how the questions were stored would be most beneficial.
0
 
sunghkimAuthor Commented:
Here is the sample database:

Q1   Q2   Q3   Q4
1    yes  yes  good
1    no   yes  bad
2    no   yes  bad
3    no   no   bad
3    yes  no   medium



Can you work with thses examples?

so with one select statement, can you get Q1 has 2 1's, 1 2's and 2 3's and
Q2 has 2 yes's and 3 no's and
Q3 has 3 yes's and 2 no's and
Q4 has 1 good's, 3 bad's and 1 medium's.

thanks.
 
0
 
geotigerCommented:
Just use the following query statement:

$tab = "web_user_log;

$q1 = "select to_char(q1), count(q1) from $tab group by Q1";

$q2 = "select q2, count(q2) from $tab group by q2";

$q3 = "select q3, count(q3) from $tab group by q3";

$q4 = "select q4, count(q4) from $tab group by q4";

$sqlquery = "$q1 union $q2 union $q3 union $q4";

You can use Maneshr's suggestion to connect to the database and execue the above statement. This worked in Oracle database.
0
 
tgoetzeCommented:
In your latest post, you seem to want each question's results handled independently. Doing this with 1 SQL query requires that you join the table to itself 4 times. This is extrememly inefficient.

If the results table is reasonably sized, you could just select the entire contents into a perl hash, and do the computations yourself. This satisfies your requirement of using just one query. The details follow:

#Assuming you can get connected to the database with Perl
$sqlquery="select Q1, Q2, Q3, Q4 from RESULTS_TABLE";

$csr=$lda->prepare($sqlquery) || warn $DBI::errstr;

$csr->execute();

#load up the data into a hash
while($q1,$q2,$q3, $q4)=($csr->fetchrow())){
  $results{'Q1'}{$q1}++;
  $results{'Q2'}{$q2}++;
  $results{'Q3'}{$q3}++;
  $results{'Q4'}{$q4}++;
}
# now you can close the database connection and do the computations

foreach $question qw('Q1', 'Q2', 'Q3', 'Q4') {
    print "\nResults for $question:\n";
    foreach $result (keys %{$results{$question}}) {
        print "    $result was selected $results{$question}{$result} times.\n";
    }
}


0
 
geotigerCommented:
Sunqhkim,

Have you tried the SQL statement? Here is what I tried in SQL*PLUS:


$ more tst02.sql

Q1   Q2   Q3   Q4
1    yes  yes  good
1    no   yes  bad
2    no   yes  bad
3    no   no   bad
3    yes  no   medium


SQL> create table b (q1 number, q2 varchar2(10),
  2  q3 varchar2(10), q4 varchar2(10));

Table created.

SQL> insert into b (q1,q2,q3,q4) values(1, 'yes', 'yes', 'good');

1 row created.

SQL> insert into b (q1,q2,q3,q4) values(1,'no','yes','bad');

1 row created.

SQL> insert into b (q1,q2,q3,q4) values(2,'no','yes','bad');

1 row created.

SQL> insert into b (q1,q2,q3,q4) values(3,'no','no','bad');

1 row created.

SQL> insert into b (q1,q2,q3,q4) values(3,'yes','no','medium');

1 row created.

SQL> select * from b;

        Q1 Q2         Q3         Q4
---------- ---------- ---------- ----------
         1 yes        yes        good
         1 no         yes        bad
         2 no         yes        bad
         3 no         no         bad
         3 yes        no         medium
  1  select to_char(q1), count(q1) from b group by q1
  2  union
  3  select q2, count(q2) from b group by q2
  4  union
  5  select q3, count(q3) from b group by q3
  6  union
  7* select q4, count(q4) from b group by q4
SQL> /

TO_CHAR(Q1)                               COUNT(Q1)
---------------------------------------- ----------
1                                                 2
2                                                 1
3                                                 2
bad                                               3
good                                              1
medium                                            1
no                                                2
no                                                3
yes                                               2
yes                                               3

10 rows selected.

SQL>

  1  select 'q1:' || to_char(q1), count(q1) from b group by q1
  2  union
  3  select 'q2:' || q2, count(q2) from b group by q2
  4  union
  5  select 'q3:' || q3, count(q3) from b group by q3
  6  union
  7* select 'q4:' || q4, count(q4) from b group by q4
SQL> /

'Q1:'||TO_CHAR(Q1)                           COUNT(Q1)
------------------------------------------- ----------
q1:1                                                 2
q1:2                                                 1
q1:3                                                 2
q2:no                                                3
q2:yes                                               2
q3:no                                                2
q3:yes                                               3
q4:bad                                               3
q4:good                                              1
q4:medium                                            1

10 rows selected.


0
 
sunghkimAuthor Commented:
i think I like tgoetze solution the best. but can you show me how to do it using "fetchall_arrayref()" instead of using fetchrow()?

thanks
0
 
sunghkimAuthor Commented:
thanks tgoetze.
I will accept your comment as an answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.