Sort column values in a particular way-CodePudding

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.

CodePudding user response：

You need some data to tell SQL Server how to sort the courses.

Create a third table, T3

| course | course_order |
 -------- -------------- 
| Math   | 1            |
| Phys   | 2            |
| Chem   | 3            |

Then your query should 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]
Join T3 On T2.[course] = T3.[course]
Group By T1.[Student ID], T1.[Student Name], T2.Course
Order By T1.[Student Name], T3.course_order

CodePudding user response：

try 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], T2.Course

CodePudding user response：

You can use a CASE expression to sort. For example:

select ...
from ...
where ...
order by T1.[Student Name], 
case T2.course when 'Math' then 1 when 'Phys' then 2 else 3 end
;

Note - be consistent. None of "Mathematics,Physics and Chemistry" exists in your table. You might also notice that this is not very flexible. Add another course (e.g., Bio) to your table and what will happen?

CodePudding user response：

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