How to choose the right row

Hello,
I'm thinking I need a case statement but I'm not sure where to put it. So far I have:

CASE WHEN ISNUMERIC(a.grade) = 1 THEN b.GradeType = 'Numeric'
else b.GradeType = 'Alpha'
End

I have two tables: table A has studentID, class, and grade. Table B has GradeType and Average.

Table A
StudentID   class   grade
125           003      75
456           004      82
854           005      A

Table B
GradeType   Average
Numeric        89
Alpha          4

GradeType is either Numeric or Alpha. My problem is some grades are Numeric and some are Alpha so when a grade is Numeric (or Alpha) it needs to also pick up what ever the average is for Numeric (or Alpha). I'm not sure how to accomplish this. I was thinking a case statement should be in either the from in the join or the where clause. If anyone could point me in the right direction. Thanks!

First off, it is considered bad practice to mix data types in one column. It would be better to have two columns: one for numeric grades and another for alphabetic grades. Actually the second column can be computed from the first.

Here's an example of a way to do that:

declare @TableA table (StudentID int,   class char(3),   grade_numeric int, grade_alpha char(2))

insert into @TableA(StudentID, class, grade_numeric, grade_alpha) values
--StudentID   class   grade
(125, '003', 75, null),
(456, '004', 82, null),
(854, '005', null, 'A')
;

with cte as
(
select StudentID, class
     , case when grade_numeric is null and grade_alpha is not null
          then case grade_alpha
               when 'A' then 98
               when 'B' then 88
               when 'C' then 78
               when 'D' then 68
               when 'E' then 55
               else 0
          end
          else grade_numeric
      end as grade_numeric
     , case when grade_numeric is not null and grade_alpha is null
          then case 
               when grade_numeric > 92 then 'A'
               when grade_numeric > 82 then 'B'
               when grade_numeric > 72 then 'C'
               when grade_numeric > 62 then 'D'
               when grade_numeric > 52 then 'E'
               else 'F'
          end 
          else grade_alpha
      end as grade_alpha
      
from @TableA
)

select * from cte

Not quite what I'm looking for but, thanks! I have no control over the database or what the teachers put into it. I just need the corresponding average to whether its a numeric grade or an alpha grade. I have updated it to:

	CASE WHEN ISNUMERIC(a.grade) = 1 THEN (select b.gradetype from table b where b.GradeType='Numeric')
else (select b.gradetype from table b where b.GradeType='Alpha')
End,

Its in the select statement but now I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

You should scold your teachers for giving you a bad design to work with!

However, you can use what I posted to build your final query showing the averages.

1 Like

Note that ISNUMERIC is not reliable. Better to use

NOT LIKE '%[^0-9]%'

instead

Thanks! I will try this.