Solved

Database Question

Posted on 2000-05-10
14
174 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now