Sort column values in a particular way

I have 2 tables:

Student ID   Student Name
-------------------------
12           John Smith
14           Raj Sharma
15           Lee Wang
16           Anan Obi


| name       | course | avg (points) |
+------------+--------+--------------+
| Anan Obi   | Math   | 96.50000     |
| Anan Obi   | Phys   | 58.00000     |
| John Smith | Math   | 86.00000     |
| John Smith | Phys   | 63.00000     |
| John Smith | Chem   | 92.50000     |
| Lee Wang   | Phys   | 78.50000     |
| Lee Wang   | Chem   | 65.00000     |
| Raj Sharma | Math   | 75.00000     |
| Raj Sharma | Phys   | 78.00000     |
| Raj Sharma | Chem   | 83.00000     |

I want to get average of points for each student and each course and sort the result by Student ID and Course in order (Mathematics,Physics and Chemistry).

The output should be the following: For each Student the course order should be (Math,Phys, Chem) and also it should be ordered by Student Name.

| name       |course| avg (points)|
+------------+------+--------------+
| Anan Obi   | Math | 96.50000 |
| Anan Obi   | Phys | 58.00000 |
| John Smith | Math | 86.00000 |
| John Smith | Phys | 63.00000 |
| John Smith | Chem | 92.50000 |
| Lee Wang   | Phys | 78.50000 |
| Lee Wang   | Chem | 65.00000 |
| Raj Sharma | Math | 75.00000 |
| Raj Sharma | Phys | 78.00000 |
| Raj Sharma | Chem | 83.00000 |

How do I achieve the same?

I wrote the following query but not able to sort Course n desired way.

select T1.[Student Name], T2.Course, avg(T2.Points as float) as 'avg (points)'
from T1
join T2 on T1.[Student ID]= T2.[Student ID]
Group by T1.[Student ID], T1.[Student Name], T2.Course
order by T1.[Student Name]

Can someone please help.

Answer

whenever there is a given desired sequence based on some string or some value which can’t be achieved by normal order by asc or order by desc then you need to have your curated ordering by using case when then in your order by clause. Something like this

order by T1.[Student Name]
,   case T2.Course when  'Math' then 1
         when  'Phys' then 2
         when  'Chem' then 3
         else 4 end

So your whole query would look like this.

select T1.[Student Name], T2.Course, avg(T2.Points as float) as 'avg (points)'
from T1
JOIN T2 ON T1.[Student ID]= T2.[Student ID]
Group by T1.[Student ID], T1.[Student Name], T2.Course
order by T1.[Student Name]
,   case T2.Course when  'Math' then 1
         when  'Phys' then 2
         when  'Chem' then 3
         else 4 end