healthiq healthiq - 6 days ago 5
SQL Question

Count condition met

0
down vote
favorite
I have a table (stu_grades) that stores student data and their grades at the centers they attended

I want to find out how many times for e.g. each student in that table got 'A' and then 'B' etc at any center

stu_grades

stu_ID|grade1|grade2|Grade3|center
1 A A C 1
2 B B B 2
3 C C A 1
1 C A C 2


the same student could occur more than once in the table with the same grades or even a different grade, same or different center

I especially want to check where the grade has appeared more than 3 or more times and how many centeres they exist in

So the final output should be like:

Stu_ID|Grade|Count|centercount
1 A 3 2 (As they accquired 'A' from 2 centres)
1 C 3 2
2 B 3 1 (As they only exist in 1 centre)
3 C 2 1
3 A 1 1

Answer
select 
    stu_id, 
    grade, 
    sum(count) count, 
    count(distinct center) centercount
from (
    select stu_id, grade, center, count(*)
    from stu_grades,
    lateral unnest(array[grade1, grade2, grade3]) grade
    group by 1, 2, 3
    ) s
group by 1, 2
order by 1, 2;

Test it here.