I have a table that I want to sort based on the average of 3 columns. For example:

select * from table order by ((col1+col2+col3)/3);

HOWEVER, if any of the three columns are null, then the null column should not be averaged, which means if col1 was NULL, it would end up dividing by 2 instead of three.

Is there any way to modify the divisor in the above?

EDIT: I tried to solve this as:

select * from table order by (IFNULL(col1,0)+IFNULL(col2,0)+IFNULL(col3,0))/(IF(col1>0,1,0)+IF(col2>0,1,0)+IF(col3>0,1,0))` 

It didn't throw any errors but didn't work (always divided by 3). Curious if anybody can explain why above wouldn't work? (aside from the div by 0 which never happened)

I have a table that I want to sort based on the average of 3 columns. For example:

select * from table order by ((col1+col2+col3)/3);

HOWEVER, if any of the three columns are null, then the null column should not be averaged, which means if col1 was NULL, it would end up dividing by 2 instead of three.

Is there any way to modify the divisor in the above?

EDIT: I tried to solve this as:

select * from table order by (IFNULL(col1,0)+IFNULL(col2,0)+IFNULL(col3,0))/(IF(col1>0,1,0)+IF(col2>0,1,0)+IF(col3>0,1,0))` 

It didn't throw any errors but didn't work (always divided by 3). Curious if anybody can explain why above wouldn't work? (aside from the div by 0 which never happened)

• mysql
• sorting
• mariadb
• average

Share Follow edited Mar 8 at 21:29 S.ov asked Mar 7 at 19:14 S.ovS.ov 41033 silver badges88 bronze badges 4

• what result do you want if all three columns are null? – ysth Commented Mar 7 at 19:31
• The need to do this suggests that the table might not be normalized well. – Barmar Commented Mar 7 at 23:06
• Hmmm, if all three columns are null I guess either at the beginning or end - if there are options. – S.ov Commented Mar 8 at 21:19
• NOTE that I'm running: mysql Ver 15.1 Distrib 5.5.68-MariaDB Let me know if any features being used won't work on that version of MariaDB. – S.ov Commented Mar 8 at 21:25

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 3

SELECT *
FROM tablename
ORDER BY (SELECT AVG(val)
          FROM ( SELECT col1 UNION ALL
                 SELECT col2 UNION ALL
                 SELECT col3
                 ) tmp (val)
          )

This would get out of hand past three or four columns but it works without needing a subquery:

coalesce(
    (c1+c2+c3)/3,
    (c1+c2)/2, (c1+c3)/2, (c2+c3)/2,
    c1, c2, c3, 0
)

This is just:

(coalesce(col1,0) + coalesce(col2,0) + coalesce(col3,0)) /
    (3 - (col1 is null) - (col2 is null) - (col3 is null))

(which will produce null if all three are null)

select *
  ,(select avg(col)ag 
    from (select col1 col union all select col2 union all select col3)tv
    -- where col is not null  -- this is not necessary
  ) avgValue
from test t
order by avgValue

col1	col2	col3	avgValue
2	2	3	2.3333
3	null	7	5.0000
5	9	1	5.0000
4	5	9	6.0000
6	null	null	6.0000
7	8	null	7.5000

fiddle