Compare the same id with 2 values in string in one table

I have a table like this:

May I know how can I display how many ids is fitting with the condition:
The grade is sorted like this A > B > C > D > F,
and the Overall grade must be greater than or equal to the Current grade

Is it need to use CASE() to switch the grade to a number first?
e.g. A = 4, B = 3, C = 2, D = 1, F = 0

In the table, there should be 345 is not match the condition. How can I display the tables below:

and\

Thanks.

As grade is sequential you can do order by desc to make the number. for the first result you can do something like below

select 
sum(case when GradeRankO >= GradeRankC then 1 else 0 end) AS 
qty_pass_the_condition,
sum(case when GradeRankO < GradeRankC then 1 else 0 end) AS 
qty_fail_the_condition,
count(*) AS total_ids
from
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankO
from YourTbale
) as a where Status='Overall'
) as b

inner join

(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankC
from YourTbale
) as a where Status='Current'
) as c on b.Id=c.Id

For second one you can do below

select 
b.Id fail_id
from
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankO
from Grade 
) as a where Status='Overall'
) as b

inner join

(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankC
from Grade 
) as a where Status='Current'
) as c on b.Id=c.Id

where GradeRankO < GradeRankC