Solved

Database Question

Posted on 2000-05-10
14
179 Views
Last Modified: 2010-03-05
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
Comment
Question by:sunghkim
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 8

Expert Comment

by:shlomoy
ID: 2798960
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
 
LVL 16

Expert Comment

by:maneshr
ID: 2800353
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
 

Author Comment

by:sunghkim
ID: 2801217
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 6

Expert Comment

by:christopher sagayam
ID: 2801289
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
 
LVL 16

Expert Comment

by:maneshr
ID: 2801299
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
 

Author Comment

by:sunghkim
ID: 2801380
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
 
LVL 3

Expert Comment

by:tgoetze
ID: 2802835
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
 

Author Comment

by:sunghkim
ID: 2803207
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
 
LVL 12

Expert Comment

by:geotiger
ID: 2809388
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
 
LVL 3

Expert Comment

by:tgoetze
ID: 2809515
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
 
LVL 12

Expert Comment

by:geotiger
ID: 2811875
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
 

Author Comment

by:sunghkim
ID: 2824532
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
 
LVL 3

Accepted Solution

by:
tgoetze earned 150 total points
ID: 2826361
# 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
 

Author Comment

by:sunghkim
ID: 2828385
thanks tgoetze.
I will accept your comment as an answer.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question