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

Question

I have a table like this:

id	status	grade
123	Overall	A
123	Current	B
234	Overall	B
234	Current	D
345	Overall	C
345	Current	A

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:

qty_pass_the_condition	qty_fail_the_condition	total_ids
2	1	3

and\

fail_id
345

Thanks.

Answer 1

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