CODEWITHSUNDEEP
oracle-databaseselectoracle11g
Stanley Mungai
Stanley Mungai

Reputation: 4150

Select Multiple rows into one row in Oracle

I have a table that stores student results for different exams and different exam types say main exam, continuous assessment, course work etc, I need to query the table such that I get only one row for one particular exam unit with the percentage averaged depending on the number of exams the students sat for. Here is my attempted query:

select stu_reg_no, unit_code, 
       exam_unit, exam_semester, 
       student_year,  
       sum(per_centage_score)/count(per_centage_score) percentage
 from student_results_master
group by unit_code, exam_unit, 
         per_centage_score, stu_reg_no,  
         exam_semester, student_year;

Here is my resultset:

enter image description here

I have two rows for the same exam unit since one is main exam and the other course work I need my output like this:

E35/1000/2013   TFT001  COMPLEX ANALYSIS   1  1  71.04
E35/1000/2013   TFT002  LINEAR ALGEBRA     1  1  56.25

The percentage for that particular unit is added and divided by the number of exams for that particular unit. How can I achieve this?

Upvotes: 1

Views: 2782

Answers (2)

goldstar
goldstar

Reputation: 337

try this:

select stu_reg_no, unit_code, exam_unit, exam_semester, student_year,  
(select sum(per_centage_score) from student_results_master t2 where t2.exam_unit = t1.exam_unit)
/(select count(per_centage_score) from student_results_master t2 where t2.exam_unit = t1.exam_unit)
from student_results_master t1
group by unit_code, exam_unit, stu_reg_no,  exam_semester, student_year;

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

Oracle provides a built-in function for calculating average value for an expression over a set of rows - AVG(). To get the desired output you need to do the following two things:

  1. Replace sum(per_centage_score)/count(per_centage_score) with avg(per_centage_score)
  2. Remove per_centage_score column from the group by clause.

To that end, your query might look like this:

select stu_reg_no
     , unit_code
     , exam_unit
     , exam_semester
     , student_year
     , avg(percentage) percentage
 from student_results_master
group by unit_code
       , exam_unit
       , stu_reg_no
       , exam_semester
       , student_year;

Result:

STU_REG_NO    UNIT_CODE EXAM_UNIT        EXAM_SEMESTER STUDENT_YEAR PERCENTAGE
------------- --------- ---------------- ------------- ------------ ----------
E35/1000/2013 TFT001    COMPLEX ANALYSIS             1            1      71.04 
E35/1000/2013 TFT002    LINEAR ALGEBRA               1            1      56.25

Upvotes: 2

Related Questions