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.

CodePudding user response：

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