How can I compare same column value in a table?

I have a table like this

s|g
-+-
a|1
b|4
c|2
d|3

I want to add a new column, which indicates the average values in column g of other rows that have values that are greater than or equal to the current row. The resulting table looks like:

s|g|c
-+-+-
a|1|3    -> (4+2+3)/3 = 3
b|4|0    -> no rows are greater than 4 in g
c|2|3.5  -> (4+3)/2 = 3.5
d|3|4    -> (4)/1 = 4

How may I achieve this? Thank you very much!

>Solution :

You may use a self left join as the following:

SELECT T.s, T.g, COALESCE(AVG(D.g), 0) AS C
  FROM
tbl T LEFT JOIN tbl D
ON T.g<D.g
GROUP BY T.s, T.g
ORDER BY T.s

See a demo.