I have a DB table named score
All the columns are set to VARCHAR as i want to use null data, not as 0
! id ! A ! B ! avg !
-------------------------------
! 01 ! 10 ! 0 ! !
! 02 ! ! 10 ! !
! 03 ! 0 ! 10 ! !
! 04 ! 10 ! ! !
I want to get the average from that table like this
! avg !
-------
! 5 !
! 10 !
! 5 !
! 10 !
Which SQL command(s) do i need to use to achieve the above result
Thanks
SELECT (COALESCE(A, 0) + COALESCE(B, 0)) /
((CASE WHEN A IS NULL THEN 0 ELSE 1 END) +
(CASE WHEN B IS NULL THEN 0 ELSE 1 END)) AS avg
FROM score
Note:
I did not handle the case of division by zero.