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!

CodePudding user response：

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.

CodePudding user response：

Use two correlated subqueries for that

Use an INDEX on g to make the query faster

  SELECT 
`s`, `g`
, COALESCE((SELECT SUM(`g`) FROM tab1 WHERE `g` > t1.`g`) / (SELECT COUNT(*) FROM tab1 WHERE `g` > t1.`g`),0) gt
FROM tab1 t1

fiddle