Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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.
0
sunghkim
Asked:
sunghkim
  • 5
  • 3
  • 2
  • +3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
tgoetzeCommented:
# 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
 
sunghkimAuthor Commented:
thanks tgoetze.
I will accept your comment as an answer.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now