Count for each record within table

Question

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

If you unpivot the data this would be a simple aggregation. Is the data stored in this pivoted fashion somewhere? or perhaps use the Unnest function described in this solution: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql — xQbert, Nov 18 '16 at 15:39
@xQbert can you help me understand these functions.. how do i make them relevant to my scenario — healthiq, Nov 18 '16 at 15:45
I have edited the question slightly so any help is appreciated — healthiq, Nov 30 '16 at 15:17

score 1 · Answer 1 · answered Nov 18 '16 at 15:38

1

This is one approach using union all to unpivot the different grades into one column and then doing an aggregation.

select stu_id,grade,count(*) cnt 
from (
select stu_id,grade_1 grade from stu_grades
union all
select stu_id,grade_2 grade from stu_grades
union all
select stu_id,grade_3 grade from stu_grades
) t
group by stu_id,grade

answered Nov 18 '16 at 15:38

Vamsi Prabhala

48,685
4
36
58

it says grade doesnt exist? – healthiq Nov 18 '16 at 15:51
`grade` is a column alias defined in the derived table. you shouldn't get that error if you used the exact query from the answer. – Vamsi Prabhala Nov 18 '16 at 15:59

Kumar_Vikas · Accepted Answer · 2016-11-18T17:37:55.127

0

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]

edited Nov 18 '16 at 17:37

answered Nov 18 '16 at 16:08

Kumar_Vikas

837
7
16

great that works... is there a way i can limit the count so see how many are more than 3 for e.g? – healthiq Nov 18 '16 at 16:22
Please use valid standard SQL. `[dbo].[Stud_Details]` is an invalid identifier in (standard) SQL (and Postgres) – Nov 18 '16 at 17:35
@a_horse_with_no_name Yes Sir! Rightly pointed. I have updated the answer. – Kumar_Vikas Nov 18 '16 at 17:38
`[Stud_Details]` is also invalid standard SQL – Nov 18 '16 at 17:52

Count for each record within table

2 Answers2