Solved

Database Question

Posted on 2000-05-10
14
176 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hard perl script 16 154
Trying to use two loops in a perl script but not getting results need. 10 82
Migrate OTRS to newest version. 1 309
Export Variables in Perl 3 44
I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
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…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 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

12 Experts available now in Live!

Get 1:1 Help Now