CODEWITHSUNDEEP
mysql
Sukma Saputra
Sukma Saputra

Reputation: 1599

MySQL obtain an average value

My head dizzy by this problem. I have table student like this:

[teac_id] [less_id] [cl_id] [stu_id] [semester] [nilai_1] [nilai_2] [nilai_3] [nilai_4]
   3         3         1        1         1       90.00     90.00     90.00     null
   3         3         1        2         1       70.00     100.00    null      null

I use composite primary key, and question is how to get result like this:

[teac_id] [less_id] [cl_id] [stu_id] [semester] [ AVG ]
   3         3         1       1        1         (nilai_1 + nilai_2 + nilai_3) / 3
   3         3         1       2        1         (nilai_1 + nilai_2) / 2

The field don't have value cannot be a divisor. Thank advance.

Upvotes: 1

Views: 61

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186688

If number nilai_ fields is fixed (i.e. you have four nilai_1..nilai_4), you can try something like that (note that at least one field should be not null):

select teac_id, 
       less_id,
       cl_id, 
       stu_id,
       semester,
       -- average: just sum / count
       (IfNull(nilai_1, 0) + 
        IfNull(nilai_2, 0) +
        IfNull(nilai_3, 0) +
        IfNull(nilai_4, 0)) / 
        (if(nilai_1 is null, 0, 1) + 
         if(nilai_2 is null, 0, 1) +
         if(nilai_3 is null, 0, 1) +
         if(nilai_4 is null, 0, 1)) as AVG
  from MyTable

If there's case that all nilai_1..nilai_4 can be null in one record, you have to modify the query:

select teac_id, 
       less_id,
       cl_id, 
       stu_id,
       semester,

       case 
         when (nilai_1 is null) and 
              (nilai_2 is null) and
              (nilai_3 is null) and
              (nilai_4 is null) then
           -- special case: all nulls average
           null
         else
           -- average: just sum / count
          (IfNull(nilai_1, 0) + 
           IfNull(nilai_2, 0) +
           IfNull(nilai_3, 0) +
           IfNull(nilai_4, 0)) / 
             (if(nilai_1 is null, 0, 1) + 
              if(nilai_2 is null, 0, 1) +
              if(nilai_3 is null, 0, 1) +
              if(nilai_4 is null, 0, 1)) 
       end as AVG
  from MyTable

Upvotes: 3

Related Questions