Geek Answers Handbook

Can you run the Oracle AVG () function for values ​​on a single line?

I have a dataset like this:

+---------+--------+--------+
| Student | Score1 | Score2 |
+---------+--------+--------+
|       1 |     23 |     40 |
|       2 |     12 |     10 |
|       3 |     54 |     90 |
+---------+--------+--------+

I want to calculate the average of 2 points in each row. Theoretically, it's pretty simple - simple (score1 + score2/2). However, I ran into problems if one of the NULL values ​​needs to be widely used NVL. The function AVG()will take care of all this for me, but it is intended to average several lines. Is there a way to use AVG for values ​​on a single line?

Update

This is what I have at the moment, which applies to all possibilities (as far as I know). However, I think there should be a cleaner way?

SELECT 
  T1.STUDENT, 
  T1.SCORE1, 
  T1.SCORE2, 
  (NVL(T1.SCORE1,0) + NVL(T1.SCORE2,0))/DECODE((NVL2(t1.SCORE1,1,0) + NVL2(t1.SCORE2,1,0)),0,NULL,(NVL2(t1.SCORE1,1,0) + NVL2(t1.SCORE2,1,0))) AS AVG_SCORE 
FROM STUDENTS T1;
+4
source share
4

. :

select student,
       ((coalesce(score1, 0) + coalesce(score2, 0)) /
        nullif(nvl2(score1, 1, 0) + nvl2(score2, 1, 0), 0)
       ) as score_avg

( ) , .

, , .

+1

:

select student,
       case 
       when score1 is null and score2 is not null then score2
       when score1 is not null and score2 is null then score1
       when score1 is null and score2 is null then 0
       else (score1 + score2)/2 end
from your_table
+1

UNION ALL, , AVG/GROUP BY:

SELECT student, avg(score) AS score
  FROM (SELECT T1.STUDENT, 
               T1.SCORE1 AS score
          FROM STUDENTS T1
        UNION ALL
        SELECT T1.STUDENT, 
               T1.SCORE2 AS score
          FROM STUDENTS T1)
 GROUP BY student
 ORDER BY student
+1

?

select student, avg(scorevalue) from
(select s1.student, 1 as scorefield, s1.score1 as scorevalue from students s1
union
select s2.student, 2 as scorefield, s2.score2 as scorevalue from students s2)
group by student
+1

More articles:


All Articles