How to get the count of the people have a better salary than the current tuple

Question

I was working on a problem from Leetcode #185

I could understand the solution but I want to know how to write the query that add a column which indicate the count the people have a better salary than the tuple one. I think it is possible in SQL, but i don't know how to make it right, i always get syntax error. :-/

from Employee e1 (Select count(distinct e2.Salary)
                  from Employee e2
                  Where e2.Salary > e1.Salary) as c

For exemple I have such a table Employee:

Id - Name - Salary
1    toto   60000
2    tata   50000
3    kiki   90000
4    lily   70000
5    momo   60000

I want to have such a result:

Id - Name - Salary - Head_count_of_higher_salary
1    toto   60000      2
2    tata   50000      4
3    kiki   90000      0
4    lily   70000      1
5    momo   60000      2

Thanks guys

Answer 1

Your subquery is almost correct.
Just remove DISTINCT from COUNT() (although just COUNT(*) would also work) and use it as the new column:

select *,  
  (
    select count(e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
  ) as Head_count_of_higher_salary
from Employee e1  

See the demo.