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

TingL 0
Viewed 38

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.

conditional-aggregation tsql sql-server sql
2 Answers

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
Nagib Mahfuz0

You can use pretty simple conditional aggregation for this, there is no need for window functions.

  • A Pass is when the row of Overall has grade which is less than or equal to Current, with "less than" being in A-Z order.
  • Then aggregate again over the whole table, and qty_pass_the_condition is simply the number of non-nulls in Pass. And qty_fail_the_condition is the inverse of that.
SELECT
  qty_pass_the_condition = COUNT(t.Pass),
  qty_fail_the_condition = COUNT(*) - COUNT(t.Pass),
  total_ids = COUNT(*)
FROM (
    SELECT
      t.id,
      Pass = CASE WHEN MIN(CASE WHEN t.status = 'Overall' THEN t.grade END) <=
                       MIN(CASE WHEN t.status = 'Current' THEN t.grade END)
                  THEN 1 END
    FROM YourTable t
    GROUP BY
      t.id
) t;

To query the actual failed IDs, simply use a HAVING clause:

SELECT
  t.id
FROM YourTable t
GROUP BY
  t.id
HAVING MIN(CASE WHEN t.status = 'Overall' THEN t.grade END) >
       MIN(CASE WHEN t.status = 'Current' THEN t.grade END);

db<>fiddle

Charlieface0
Related