CODEWITHSUNDEEP
sql
zhinee
zhinee

Reputation: 135

SQL query question on selecting grade for student's academic history

If I have a student_grade_history table containing past grade history for students, for example:

student | course   | year       | grade 
--------+----------+------------+----------
steve   | Math1A   | 2018spring |  A+   
steve   | English  | 2018spring |  B-   
steve   | Science  | 2018spring |  B+  
steve   | Biology  | 2018spring |  C

and I would like to count the grade each student received A+, A , A- into grade A and B+,B,B- into grade B and C+,C,C- into grade C.

I am able to create a table like the following: 

student | year       | # of A received | # of B received | # of C received 
--------+------------+-----------------+-----------------+-----------------
steve   | 2018spring |       1         |        2        |        1


However, I am trying to creating a table that has the following format based on student_grade_history table, but I cannot think of a way to do it. 

student | year       | grade | count  
--------+------------+-------+------
steve   | 2018spring |   A   |   1      
steve   | 2018spring |   B   |   2      
steve   | 2018spring |   C   |   1

Can I get some hints how to approach this?

Upvotes: 0

Views: 465

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You seem to want aggregation:

select student, year, left(grade, 1) as grade, count(*)       
from student_grade_history
group by student, year, left(grade, 1);

Not all databases support left() (but most do). All have the functionality, even if it goes by a slightly different syntax.

Upvotes: 1

Related Questions