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
First, count the triples (student, grade, center):
select stu_id, grade, center, count(*)
from stu_grades
cross join unnest(array[grade1, grade2, grade3]) as grade
group by 1, 2, 3
order by 1, 2, 3;
Then use this data to calculate the expected aggregates:
select
stu_id,
grade,
sum(count) as count,
count(distinct center) as centercount
from (
select stu_id, grade, center, count(*)
from stu_grades,
lateral unnest(array[grade1, grade2, grade3]) as grade
group by 1, 2, 3
) s
group by 1, 2
order by 1, 2;
Test it in db<>fiddle.