Comparing differences in characters in SQL (or AWS QuickSight)

Question

We are using Postgres and on our student table, we have columns for letter grades [A, B, C, D, F]. We have two columns for previous grade and the current grade. I'm trying to find a way to run a report on how much the grades have changed for each record. So essentially, I want a field for current_grade-previous_grade but since the values are text, I can't do that comparison easily. I was hoping that parseInt in AWS QuickSight will parse a value for the grades but the documentation says it ignores non-numerical values. Since AWS QuickSight doesn't seem to solve this problem, how would I do this in my query in SQL when I don't have write access and can't create generated columns for this? I was hoping for something simple like:

SELECT id, report_card->'grade' as current_grade, report_card->'previous_grade' as old_grade, old_grade-current_grade FROM Students;

If you have non-numerical values in grades, how do you plan on comparing them? Can you show some sample data? — Phil Coulson
They'll just be a letter grade like "A", "B","C","D" for each column. I just want to know the difference. Some languages would take the ASCII key for them and do a minus on them. For example, B-A would translate to 066-065 and result in 1. I'm thinking something along those lines. — Jorjani

Phil Coulson Phil Coulson · Accepted Answer · 2020-08-26T16:38:39

Quicksight seems to support locate which you might be able to tweak to your advantage. It's as if you're assigning numbers to grades in current_grade and previous_grade column

locate('ABCDEF', current_grade) - locate('ABCDEF', previous_grade)

If that doesn't work, they also have ifelse, which you could modify along these lines

ifelse(current_grade="A", 1, current_grade="B", 2, ......,current_grade="F", 6) - 
ifelse(previous_grade="A", 1, previous_grade="B", 2, ......,previous_grade="F", 6)

Comparing differences in characters in SQL (or AWS QuickSight)

1 Answers