healthiq healthiq - 10 days ago 7
SQL Question

Count for each record within table

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

This should satisfy your requirement:

SELECT [Stud_ID], Grade, count(*) as GradeCount FROM
  (SELECT [Stud_ID],Grade1 as Grade from [Stud_Details]
   UNION ALL
   SELECT [Stud_ID],Grade2 as Grade from [Stud_Details]
   UNION ALL
   SELECT [Stud_ID],Grade3 as Grade from [Stud_Details]) AS T
   GROUP BY T.[Stud_ID],T.Grade
   ORDER BY T.[Stud_ID]

UPDATE:

You can use HAVING after GROUP BY to get gradeCount which are greater than 3.

SELECT [Stud_ID], Grade, count(*) as GrdCountStud FROM
  (SELECT [Stud_ID],Grade1 as Grade from [Stud_Details]
   UNION ALL
   SELECT [Stud_ID],Grade2 as Grade from [Stud_Details]
   UNION ALL
   SELECT [Stud_ID],Grade3 as Grade from [Stud_Details]) AS T
   GROUP BY T.[Stud_ID],T.Grade
     HAVING COUNT(*) > 3   
   ORDER BY T.[Stud_ID]