Re: Data Calculation - Mailing list pgsql-sql

According to your scenario I did the following

create table answerkey (
question_number int,
answer varchar(2));
create table studentanswer (
student_id varchar(6),
answer01 varchar(2),
answer02 varchar(2));
insert into answerkey values(1,'A');
insert into answerkey values(2,'B');
insert into studentanswer values ('010019','B','C');
insert into studentanswer values ('029393','B','B');

create view studentanswer_boolean as 
select student_id,
answer01 = (select answer from answerkey where question_number=1) as a01,
answer02 = (select answer from answerkey where question_number=2) as a02
from studentanswer ;
select * from studentanswer_boolean ;

create view studentanswer_numeric as 
select student_id,
case when answer01 = (select answer from answerkey where question_number=1) then 1 else 0 end as a01,
case when answer02 = (select answer from answerkey where question_number=2) then 1 else 0 end as a02
from studentanswer ;
select * from studentanswer_numeric ;

create view studentanswer_sumcorrect as 
select student_id,
(case when answer01 = (select answer from answerkey where question_number=1) then 1 else 0 end)+
(case when answer02 = (select answer from answerkey where question_number=2) then 1 else 0 end)
as "sum_of_correct_answers" 
from studentanswer ;

HTH

About your second scenario: 
Have you seen PL/Perl - Perl Procedural Language within the documentation? 

Regards, Christoph 

> I need some help for which I could not find much help for within the
> Postgres book I am looking at, or the online tutorial. I don't know if
> this is even possible, but here goes...
> 
> I am writing for advice, as the method I followed is not the most
> effecient, I think.
> 
> Scenario: Grading results. I have two tables set up - one with the
> answerkey and one with the students results from a test:
> 
> answerkey:
> 
> question_number int,
> answer varchar(2)
> 
> So something like this (select * from answerkey):
> 
> 1 | 2 | 3 ...... | 30
> ------------------
> A | B | C..... | D
> 
> Student results are similar as the answerkey table, but contain a unique
> student id:
> 
> student_id | 1 | 2 | 3 .....
> -------------------------
> 010019 | B | C | C ....
> 029393 | B | B | C.....
> 
> Currently, to calculate how each student did, within Perl, I obtain the
> table results in an array and do an array calculation:
> 
> if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
> 
> This works fine and is quite fast, though could postgres be used to do
> this faster?
> 
> The reason being is that once I have the number of correct answers for a
> student, I then calculate the % score for each student, based on the
> weight of the question (also in another table).
> 
> Now, all this data that Perl calculates is displayed for the end user in a
> table. The user can also sort by a field, such as %.
> 
> Because Perl did the % calculations, I have to re-calculate everything and
> dump it into a temporary table, to make sorting easier:
> 
> student_id, answer_correct, weights_score, percentage_score
> 
> Then, if the user wants to sort by the percentage field, I do a select *
> from temp_answers order by $field.
> 
> This works fine, and of a class with 500 students, all this happens in
> about 10 seconds.
> 
> I am new to the Postgres world, and am just wondering: is there any way I
> can make Postgres do all the calculations, therefore, bypassing the need
> to create a temporary table upon every lookup, just for sorting purposes?
> 
> A second scenario is this. I have a list of 12,000 students. The end user
> selects what criteria to search for (ie. to look up students belonging in
> a certain class and/or teacher). The select is fine and works, however,
> then the user needs to be taken to a reports page, where this a different
> Perl program running for each different report.
> 
> How do I pass this SQL statement to the perl programs? Currently, I select
> the students that match the criteria and put their IDs into a temporary
> table and pass the name of this table name to the other perl programs. Is
> there a way to bypass this creation of a table?
> 
> Thank you very much for your time.
> 
> Ogden Nefix
>